Início / dba / Perguntas / 343375
Accepted
Dante
Asked: 2024-11-02 11:27:04 +0800 CST

Evite inserir ID pai errado

  • 772

Tenho duas tabelas em um banco de dados Postgres 17: sitee page. Cada site pode ter várias páginas e cada página pode ter uma página pai:

CREATE TABLE site (
  id INT PRIMARY KEY
);
CREATE TABLE page (
  id INT PRIMARY KEY,
  site_id INT references site(id),
  parent_id INT references page(id)
);

Agora suponha que eu tenha dois sites:

INSERT INTO site (id) VALUES (1);
INSERT INTO site (id) VALUES (2);

E uma página raiz para cada site:

INSERT INTO page (id, site_id, parent_id) VALUES (1, 1, NULL);
INSERT INTO page (id, site_id, parent_id) VALUES (2, 2, NULL);

Como evitar a inserção de uma nova página para o site 2 com uma página pai que pertence ao site 1?

INSERT INTO page (id, site_id, parent_id) VALUES (3, 2, 1);
postgresql

1 respostas

  1. Best Answer

    Para garantir que a página pai aponte para o mesmo site:

    CREATE TABLE site (
  id int PRIMARY KEY
);

CREATE TABLE page (
  id        int PRIMARY KEY
, site_id   int NOT NULL
, parent_id int
, CONSTRAINT page_site_id_fk FOREIGN KEY (site_id) REFERENCES site
, CONSTRAINT page_site_id_parent_id_uniq UNIQUE (id, site_id)  -- needed for following FK
, CONSTRAINT page_hierarchy_fk FOREIGN KEY (parent_id, site_id) REFERENCES page (id, site_id)
, CONSTRAINT page_no_self_rel CHECK (parent_id <> id)  -- disallow self-reference
);

    violino

    A restrição FK multicoluna page_hierarchy_fkadiciona site_idà referência, então o pai deve apontar para o mesmo site. Esta FK requer uma UNIQUErestrição redundante em page(id, site_id).

    Também coloquei uma CHECKrestrição para proibir auto referências. Agora você não pode criar círculos com inserções de linha única. Mas você ainda pode criar círculos em dois ou mais hubs com inserções de várias linhas ou com um UPDATE. Se isso for um problema, crie um gatilho (potencialmente caro) que percorra o gráfico em insert / update para descartar loops infinitos.

    Relacionado:

    • 4

relate perguntas