Início / dba / Perguntas / 29147
Accepted
Haseena
Asked: 2012-11-23 22:36:52 +0800 CST

PostgreSQL - inserir/atualizar viola restrições de chave estrangeira

  • 772

Eu sou um novo no postgreSQL. Eu tenho 3 tabelas, uma tabela referencia as chaves primárias das outras 2 tabelas. Mas não consegui inserir dados no arquivo Table3. Veja o código abaixo:

DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
  "DataID" bigint NOT NULL DEFAULT '0',
  "AdData" integer DEFAULT NULL,
  PRIMARY KEY ("DataID")
);

DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
  "Address" numeric(20) NOT NULL DEFAULT '0',
  "Value" numeric(20) DEFAULT NULL,
  PRIMARY KEY ("Address")
);

DROP TABLE IF EXISTS Table3 CASCADE; 
CREATE TABLE table3 (   
  "ID" bigint NOT NULL DEFAULT '0',   
  "DataID" bigint DEFAULT NULL,   
  "Address" numeric(20) DEFAULT NULL,   
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),   
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,   
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

ERRO: inserir ou atualizar na tabela "Table3" viola a restrição de chave estrangeira "Table3_DataID_fkey" DETALHE: A chave (DataID)=(27856) não está presente na tabela "Table1".

Quando tentei inserir dados nas 3 tabelas, ocorreu um erro. Consultei a documentação do postgreSQL e alterei meu código da seguinte forma: (Infelizmente apresentou outro erro)

DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
  "DataID" bigint NOT NULL DEFAULT '0',
  "AdData" integer DEFAULT NULL,
  PRIMARY KEY ("DataID")
);

DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
  "Address" numeric(20) NOT NULL DEFAULT '0',
  "Value" numeric(20) DEFAULT NULL,
  PRIMARY KEY ("Address")
);

DROP TABLE IF EXISTS Table3 CASCADE; 
CREATE TABLE table3 (   
  "ID" bigint NOT NULL DEFAULT '0',   
  "DataID" bigint DEFAULT NULL REFERENCES Table1 ON DELETE RESTRICT,
  "Address" numeric(20) DEFAULT NULL REFERENCES Table2 ON DELETE CASCADE, 
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),   
   PRIMARY KEY("DataID", "Address")
);

ERRO: várias chaves primárias para a tabela "Table3" não são permitidas LINE 65: PRIMARY KEY("DataID", "Address")

Por favor me ajude... Como posso criar a referência?

Mudei o IDas UNIQUEe removi a linha PRIMARY KEY ("ID"). Naquela época, ele mostra outro erro como:

ERRO: o valor de chave duplicado viola a restrição exclusiva "Table3_pkey"

postgresql foreign-key

2 respostas

  1. Best Answer

    Existem alguns problemas com suas tabelas. Vou tentar abordar as chaves estrangeiras primeiro, já que você questionou sobre elas :)

    Mas antes disso, devemos perceber que os dois conjuntos de tabelas (os três primeiros que você criou e o segundo conjunto, que você criou depois de descartar o primeiro conjunto) são os mesmos. Claro, a definição de Table3na sua segunda tentativa tem erros de sintaxe e lógicos, mas a ideia básica é:

    CREATE TABLE table3 (   
  "ID" bigint NOT NULL DEFAULT '0',   
  "DataID" bigint DEFAULT NULL,   
  "Address" numeric(20) DEFAULT NULL,   
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),   
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,   
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

    Esta definição diz ao PostgreSQL aproximadamente o seguinte: "Crie uma tabela com quatro colunas, uma será a chave primária (PK), as outras podem ser NULL. Se uma nova linha for inserida, marque DataIDe Address: se contiverem um valor não NULL ( digamos 27856), então verifique Table1por DataID˙e Table2por Address. Se não houver tal valor nessas tabelas, então retorne um erro." Este último ponto que você viu primeiro:

    ERROR: insert or update on table "Table3" violates foreign key constraint 
    "Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".

    Tão simples: se não houver linha em Table1where DataID = 27856, você não poderá inserir essa linha em Table3.

    Se você precisar dessa linha, primeiro insira uma linha em Table1com DataID = 27856e só então tente inserir em Table3. Se isso não lhe parece o que você deseja, descreva em poucas frases o que você deseja alcançar, e podemos ajudar com um bom design.

    E agora sobre os outros problemas.

    Você define seus PKs como

    CREATE all_your_tables (
    first_column NOT NULL DEFAULT '0',   
    [...]
    PRIMARY KEY ("ID"),

    Uma chave primária significa que todos os itens nela são diferentes uns dos outros, ou seja, os valores são UNIQUE. Se você der uma estática DEFAULT(como '0') a uma UNIQUEcoluna, terá surpresas ruins o tempo todo. Isto é o que você obteve em sua terceira mensagem de erro.

    Além disso, '0'significa uma string de texto, mas não um número ( bigintou numericno seu caso). Use simplesmente 0em vez disso (ou não use, como escrevi acima).

    E um último ponto (posso estar errado aqui): em Table2, seu Addresscampo está definido como numeric(20). Ao mesmo tempo, é o PK da mesa. O nome da coluna e o tipo de dados sugerem que esse endereço pode ser alterado no futuro. Se isso for verdade, então é uma escolha muito ruim para um PK. Pense no seguinte cenário: você tem um endereço '1234567890454', que tem um filho Table3como

    ID        DataID           Address             Data
123       3216547          1234567890454       654897564134569

    Agora esse endereço muda para outra coisa. Como você faz sua linha filho Table3seguir seu pai para o novo endereço? (Existem soluções para isso, mas podem causar muita confusão.) Se este for o seu caso, adicione uma coluna de ID à sua tabela, que não conterá nenhuma informação do mundo real, ela servirá simplesmente como um valor de identificação (ou seja, , ID) para um endereço.

    • 24

  2. Tudo depende do que você quer fazer com os dados.

    Primeiro exemplo - você deseja ter dados consistentes em todas as tabelas, mas tenta inserir valores que não correspondem à Tabela1.

    Segundo exemplo - você não quer ter dados consistentes, mas tenta fazer outra coisa, não sabe exatamente o quê. A tabela não pode ter mais de uma chave primária.

    Terceiro exemplo - você ainda não sabe o que deseja alcançar e coloca a restrição UNIQUE na coluna que pode ter o mesmo valor várias vezes.

    Se você deseja apenas inserir seus dados - livre-se das referências de chave estrangeira no primeiro exemplo. Se você deseja ter dados consistentes em todas as tabelas - faça a limpeza de dados e insira em tabelas COM restrições de chave estrangeira.

    tl;dr: para inserir seus dados em Table3 com o código do primeiro exemplo - insira valores ausentes na coluna Table1.DataID que existe em Table3.DataId.

    • 1

relate perguntas