Вопрос по postgresql – Нужен ли первичный ключ для моей таблицы, который имеет УНИКАЛЬНЫЙ (составной 4 столбца), один из которых может быть NULL?

9

У меня есть следующая таблица (PostgreSQL 8.3), в которой хранятся цены на некоторые продукты. Цены синхронизируются с другой базой данных, в основном большинство полей ниже (кроме одного) не обновляются нашим клиентом, а вместо этого удаляются и обновляются время от времени для синхронизации с другой базой данных акций:

CREATE TABLE product_pricebands (
    template_sku varchar(20) NOT NULL,
    colourid integer REFERENCES colour (colourid) ON DELETE CASCADE,        
    currencyid integer NOT NULL REFERENCES currency (currencyid) ON DELETE CASCADE,
    siteid integer NOT NULL REFERENCES site (siteid) ON DELETE CASCADE,

    master_price numeric(10,2),

    my_custom_field boolean, 

    UNIQUE (template_sku, siteid, currencyid, colourid)
);

При синхронизации я в основном удаляю большую часть данных выше, за исключением данных, ГДЕ my_custom_field равно TRUE (если оно TRUE, это означает, что клиент обновил это поле через свою CMS и, следовательно, эту запись не следует удалять). Затем я вставляю от 100 до 1000 строк в таблицу, и ОБНОВЛЯЮЩУЮ, где СБОЙ не выполняется (т. Е. Где комбинация (template_sku, siteid, currencyid, colourid) уже существует).

У меня вопрос - какую наилучшую практику следует применять здесь для создания первичного ключа? Нужен ли первичный ключ? Я хотел сделать первичный ключ = (template_sku, siteid, currencyid, colourid) - но поле цвета может иметь значение NULL, и использование его в составном первичном ключе невозможно.

Из того, что я прочитал на других сообщениях на форуме, я думаю, что я сделал выше, правильно, и просто нужно уточнить:

1) Должен ли я использовать «серийный» первичный ключ на тот случай, если он мне понадобится? В настоящий момент я не думаю и не думаю, что когда-либо буду, потому что важными данными в таблице являются цена и мое настраиваемое поле, идентифицируемое только комбинацией (template_sku, siteid, currencyid, colourid).

2) Поскольку (template_sku, siteid, currencyid, colourid) - это комбинация, которую я буду использовать для запроса цены продукта, если я добавлю дальнейшую индексацию в мои столбцы, например & quot; template_sku & quot; какой варчар? Или УНИКАЛЬНОЕ ограничение - хороший индекс уже для моих SELECT?

Хорошо, вот простой ответ. Если у вас есть таблица с полями a, b, c, d и d, которые можно обнулять, можете ли вы жить со строками, которые имеют одинаковые поля a, b и c? Если так, то то, что у вас есть, хорошо. Если вам нужно разделить их, вам нужен уникальный индекс для a, b, c или частичный индекс для a, b, c, где d равно нулю. Scott Marlowe
Колорид обнуляется. Это делает его неприятным членом для первичного ключа. wildplasser

Ваш Ответ

1   ответ
11

serial" primary key just in case I ever need one?

Вы можете легко добавить серийный столбец позже, если он вам нужен:

ALTER TABLE product_pricebands ADD COLUMN id serial;

Столбец будет заполнен уникальными значениями автоматически. Вы даже можете сделать его первичным ключом в том же операторе (если первичный ключ еще не определен):

ALTER TABLE product_pricebands ADD COLUMN id serial PRIMARY KEY;

Если вы ссылаетесь на таблицу из других таблиц, я бы посоветовал использовать такойsurrogate primary keyпотому что это довольно громоздко для ссылки на четыре столбца. Это также медленнее в SELECTs с JOIN.

В любом случае, выshould define a primary key, УНИКАЛЬНЫЙ индекс, включающий обнуляемый столбец, не является полной заменой. Это позволяет дублировать комбинации, включающие значение NULL, потому что два значения NULL никогда не считаются одинаковыми. Это может привести к неприятностям.

Как

the colourid field can be NULL

вы можете создатьtwo unique indexes, Комбинация(template_sku, siteid, currencyid, colourid) не может бытьPRIMARY KEYиз-за обнуляемогоcolourid, но вы можете создатьUNIQUE ограничение, как у вас уже есть (реализация индекса автоматически):

ALTER TABLE product_pricebands ADD CONSTRAINT product_pricebands_uni_idx
UNIQUE (template_sku, siteid, currencyid, colourid)

Этот индекс прекрасно охватывает запросы, которые вы упоминаете в 2).
Кроме того, создайте частичный уникальный индекс, если хотите избежать & quot; дубликатов & quot; с(colourid IS NULL):

CREATE UNIQUE INDEX product_pricebands_uni_null_idx
ON product_pricebands (template_sku, siteid, currencyid)
WHERE colourid IS NULL;

Чтобы охватить все базы. Я написал больше об этой технике всвязанный ответ на dba.SE.

Простая альтернатива вышесказанному состоит в том, чтобы сделатьcolourid NOT NULL и создайте первичный ключ вместо вышеуказанногоproduct_pricebands_uni_idx.

Кроме того, как вы

basically DELETE most of the data

для вашей операции пополнения, будет быстрее удалить индексы, которыеnot needed во время операции пополнения, и воссоздайте их потом. Построить индекс с нуля быстрее, чем добавлять все строки постепенно.

Как узнать, какие индексы используются (нужны)?

Test your queries with EXPLAIN ANALYZE. Or use the built-in statistics. pgAdmin displays statistics in a separate tab for the selected object.

Также может быть быстрее выбрать несколько строк сmy_custom_field = TRUE во временную таблицу,TRUNCATE базовый стол и повторно вставьте выживших. Зависит от того, определены ли у вас внешние ключи. Будет выглядеть так:

CREATE TEMP TABLE pr_tmp AS
SELECT * FROM product_pricebands WHERE my_custom_field;

TRUNCATE product_pricebands;
INSERT INTO product_pricebands SELECT * FROM pr_tmp;

Это позволяет избежать пылесоса.

Error: User Rate Limit Exceeded rishijd
Error: User Rate Limit Exceededsurrogate primary keyError: User Rate Limit ExceededifError: User Rate Limit ExceededduringError: User Rate Limit ExceededafterError: User Rate Limit Exceeded
Error: User Rate Limit Exceeded rishijd
Error: User Rate Limit Exceeded rishijd
Error: User Rate Limit Exceeded rishijd

Похожие вопросы