Início / dba / Perguntas / 339836
Accepted
ceving
Asked: 2024-05-27 23:22:12 +0800 CST

Por que meu índice exclusivo parcial não é suficiente para uma chave estrangeira?

  • 772

Eu tenho o seguinte código, que gera o erro:

ERRO: não há nenhuma restrição exclusiva correspondente às chaves fornecidas para a tabela referenciada "pessoa"

DROP TABLE IF EXISTS person;
CREATE TABLE person (
  sn        serial     PRIMARY KEY,
  id        int        NOT NULL,
  modified  timestamp,
  name      text
);
CREATE UNIQUE INDEX person_id_key ON person (id) WHERE modified IS NULL;
DROP TABLE IF EXISTS account;
CREATE TABLE account (
  id        int        NOT NULL REFERENCES person(id),
  name      text
);

Existe um índice exclusivo para id:

                                       Table "public.person"
  Column  |            Type             | Collation | Nullable |              Default               
----------+-----------------------------+-----------+----------+------------------------------------
 sn       | integer                     |           | not null | nextval('person_sn_seq'::regclass)
 id       | integer                     |           | not null | 
 modified | timestamp without time zone |           |          | 
 name     | text                        |           |          | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (sn)
    "person_id_key" UNIQUE, btree (id) WHERE modified IS NULL

Por que não é suficiente para a chave estrangeira?

postgresql

1 respostas

  1. Best Answer

    Isso é meio monstro - <TL:DR> Vá para o adendo </TL:DR>

    Sua pergunta:

    Por que não é suficiente para a chave estrangeira?

    Porque seu índice person_id_key UNIQUE, btree (id) WHERE modified IS NULLcobre apenas a exclusividade de uma fração dos person.idregistros. Para que um campo seja FOREIGN KEY, ele deve abranger todos os person.idregistros!

    Você tem que fazer as coisas de maneira um pouco diferente e ativar CREATEseu UNIQUEíndice person.idno momento da criação da tabela e CREATEdepois seu índice parcial (e com um nome diferente, obviamente).

    Todo o código está no violino aqui .

    O que você faz é isto:

    CREATE TABLE person 
(
  sn        SERIAL     PRIMARY KEY,
  id        INTEGER    NOT NULL UNIQUE,  -- CREATE the UNIQUE index at table creation!
  modified  TIMESTAMP,
  name      TEXT
);

    e então CREATEsua tabela de contas:

    CREATE TABLE account 
(
  id        INTEGER        NOT NULL REFERENCES person(id),
  name      TEXT
);

    e quando isso for bem-sucedido, agora temos CREATEnosso índice parcial:

    CREATE UNIQUE INDEX person_idx_key ON person (id) WHERE modified IS NULL;

    e então executamos a seguinte consulta, apenas para verificar:

    SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  c2.relname as "Table"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','I','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

    Resultado:

    Schema  Name           Type   Owner     Table
public  person_id_key  index  postgres  person
public  person_idx_key index  postgres  person
public  person_pkey    index  postgres  person

    Et voilá! Você pode obter o SQL acima executando \set ECHO_HIDDEN one psqlexecutando \di. Definir a ECHO_HIDDENvariável significa que quando você emite um psqlmeta-comando (ou seja, não SQL), o SQL gerado por esse comando é fornecido - é uma ótima maneira de aprender sobre o PostgreSQL!

    Alguns pontos:

    • posso apenas recomendar que você use TIMESTAMPTZ(com fuso horário) em vez de apenas TIMESTAMP- é apenas uma prática melhor - mesmo que você nunca planeje se mudar para um país/região diferente, ainda há questões de horário de verão (horário de verão) a serem consideradas. E, mesmo que você não more em uma área que não tenha horário de verão agora , isso poderá ocorrer no futuro.

    • Além disso, posso sugerir que você dê nomes significativos aos seus índices, como sufixos UNIQUEde índices com _uqou similares.

    • Isso também funciona!

    Adendo - em resposta à pergunta do OP nos comentários:

    Você pode fornecer uma referência para o primeiro parágrafo da sua resposta? A afirmação é sempre verdadeira?

    Para aqueles que esqueceram, o primeiro parágrafo foi:

    Porque seu índice person_id_key UNIQUE, btree (id) WHERE modificado IS NULL cobre apenas a exclusividade de uma fração dos registros person.id. Para que um campo seja uma FOREIGN KEY, ele deve abranger todos os registros person.id!

    Eu realmente nunca pensei sobre isso profundamente até que sua pergunta me levou a investigar - e estou muito satisfeito por ter feito isso. O que descobri surpreendeu-me, mas também estou grato por ter tido uma espécie de epifania como resultado da minha análise.

    Minha primeira pista veio aqui de @ErwinBrandstetter, onde ele escreveu:

    É seguro assumir que os valores são únicos, se você tiver um índice exclusivo definido. É assim que as restrições exclusivas são implementadas (no momento e provavelmente também em todas as versões futuras).

    Agora, observe como há uma distinção entre a UNIQUE CONSTRAINTe a UNIQUE INDEX- levei mais pesquisas, violinos e mais algumas leituras para finalmente compreendê-la.

    Ele continua dizendo:

    Definir uma restrição UNIQUE é efetivamente o mesmo (quase, veja abaixo) que criar um índice exclusivo sem especificar o tipo de índice.

    Então, novamente uma distinção - não tenho certeza exatamente o que ele quis dizer com "sem... tipo de índice".

    A partir daqui (@EB novamente), temos:

    Uma chave estrangeira deve fazer referência a colunas que sejam uma chave primária ou formem uma restrição exclusiva. Isso significa que as colunas referenciadas sempre possuem um índice (aquele subjacente à chave primária ou restrição exclusiva); portanto, verificar se uma linha de referência corresponde será eficiente.

    Então, a CONSTRAINTtem um subjacente INDEX , mas não é simplesmente um índice em si - é mais!

    Uma pista adicional pode ser encontrada aqui :

    Mais uma vantagem de usar UNIQUE INDEX vs. UNIQUE CONSTRAINT é que você pode facilmente DERRUBAR/CRIAR um índice CONCURRENTEMENTE, enquanto com uma restrição você não pode.

    e EB (primeiro link) diz:

    Restrições exclusivas podem ser adiadas. Isso não é possível para índices exclusivos.

    Portanto, são duas diferenças documentadas entre UNIQUErestrições e índices.

    Finalmente tudo se encaixou para mim quando me deparei com esta jóia - prova que há uma grande diferença entre os dois:

    SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

    Então, com relação à sua situação, modifiquei o violino original e incluí o que aprendi.

    Eu entrei na toca do coelho com os violinos, então você pode ignorar tudo, exceto o último trecho que é o SQL acima - observe como em vez de 3 registros, agora existem 2!

    Resultado do SQL (apenas os dois primeiros campos mostrados):

    oid     conname              
16392   person_sn_p_key     
16394   person_id_uq_constr

    Um deles representa o PRIMARY KEYda persontabela, e o outro é o (geral) UNIQUE CONSTRAINT- o que "falta" é o parcial UNIQUE INDEX, e isso porque NÃO é uma restrição!

    A maneira como racionalizei para mim mesmo é que CONSTRAINTexiste um recurso para impedir que coisas entrem nas tabelas - uma vez passadas todas as restrições, é simplesmente a INDEXtarefa de fornecer ponteiros para que possam ser acessados ​​​​mais rapidamente se o otimizador acha que deveria ser usado - ou seja, um mero detalhe de implementação.

    Fim do violino - mudei ALTER TABLE ADD CONSTRAINT...para CREATE INDEX...e agora há apenas 1 registro no CONSTRAINTSQL - mas a conta FKnão falha - porque cobre a tabela inteira .

    • CONSTRAINTs = proteção

    • INDEXs = organização (exceto onde cobrem toda a tabela)

    • CONSTRAINTs - exigido pelo padrão SQL

    • INDEXes - não obrigatório

    Então, para responder às perguntas

    • referências? Sim - veja links

    • dois tipos de UNIQUEíndice? Na verdade, seria mais correto dizer que existem três tipos

      • UNIQUE CONSTRAINTs - pode ser usado em FKs
      • UNIQUE INDEXes (não qualificado) - pode ser usado em FKs
      • UNIQUE INDEXes (parcial) - não pode ser usado em FKs

    Et voilà - e um +1 por realmente me fazer pensar!

    Outros links úteis:

    • 4

relate perguntas