Esta é uma tabela para um catálogo de medicamentos. Alguns têm uma marca farmacêutica, outros são genéricos (ou seja, nunca terão informações de marca)
CREATE TABLE medicine (
id serial PRIMARY KEY,
name text NOT NULL,
brand_id integer
CONSTRAINT brand_fk FOREIGN KEY (brand_id) REFERENCES brand (id)
);
CREATE TABLE brand (
id serial PRIMARY KEY,
name text NOT NULL
);
Para armazenar medicamentos genéricos, por exemplo abc
& xyz
, existem 2 opções:
Use NULL para a chave estrangeira
brand_id
INSERT INTO medicine (name, brand_id) VALUES ('abc', NULL)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', NULL)
Insira apenas 1 string vazia no nome da marca e use-a para todos os brand_id de medicamentos genéricos
INSERT INTO brand (id, name) VALUES (1, '')
INSERT INTO medicine (name, brand_id) VALUES ('abc', 1)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', 1)
Pelo que li no StackExchange, parece que 1
é a maneira geral de fazer isso. No entanto, se eu quiser um índice exclusivo sobre o nome do medicamento e o brand_id, terei que usar índices parciais (ou seja, 2 índices, respectivamente, onde brand_id IS NULL
& brand_id is NOT NULL
).
Se eu optar pela segunda abordagem, posso alcançá-la com um único índice.
Existe alguma outra vantagem/desvantagem de qualquer uma dessas abordagens. Sinto que o segundo método não é convencional e pode haver alguns problemas que descobrirei mais tarde ao longo do caminho.
PS Usei o exemplo de medicamentos para ilustrar minha consulta, mas quero entender os méritos técnicos da solução puramente do ponto de vista do banco de dados, ou seja, sem entrar em uma discussão sobre qual abordagem seria melhor para o exemplo de medicamento.
A resposta está na ambiguidade da sua pergunta :
Na verdade, você não deseja "um índice exclusivo". Você deseja impor certas regras. Um índice único é um meio para um fim ; uma ferramenta . A pergunta é: O que exatamente você quer ?
Você quer que haja apenas uma única instância de um
name
com uma marca desconhecida? Ou pode haver várias instâncias? Isso depende do que "desconhecido" ou "ausente" ou "vazio"NULL
significa para você.O significado canônico de NULL é "desconhecido". Simplesmente não sabemos, o que vai aqui, pode ser qualquer coisa , inclusive nada (vazio (
''
) em tipos string,0
em tipos numéricos).Se você quiser permitir uma marca genérica, inclua uma entrada em
brand
, chame-a de "genérica" ou " ACME " ou qualquer outra coisa. Eu gosto de usar o valor id0
para uma entrada tão abrangente.Então, seu índice ou restrição exclusivo está fazendo o que deveria: Permitir apenas uma instância de
(name, brand_id)
in the tablemedicine
. Mas múltiplas entradas com(name, NULL)
ainda são possíveis. Definabrand_id NOT NULL
, também, se você não quiser isso.A melhor solução também depende de consultas típicas e detalhes de sua configuração. Certifique-se de documentar exatamente o que NULL / empty / 0 deve significar e por que você escolheu projetá-lo dessa forma. Pode parecer claro no momento em que você implementá-lo, mas pode ser confuso depois.
Adicionei outro link à resposta que você mencionou: