Início / dba / Perguntas / 48568
Accepted
jpmc26
Asked: 2013-08-23 17:11:50 +0800 CST

Como relacionar duas linhas na mesma tabela

  • 772

Eu tenho uma tabela onde as linhas podem estar relacionadas entre si e, logicamente, a relação ocorre nos dois sentidos (basicamente, é sem direção) entre as duas linhas. (E se você está se perguntando, sim, isso realmente deveria ser uma tabela. São duas coisas exatamente da mesma entidade/tipo lógico.) Posso pensar em algumas maneiras de representar isso:

  1. Armazene a relação e seu reverso
  2. Armazene o relacionamento de uma maneira, restrinja o banco de dados de armazená-lo de outra maneira e tenha dois índices com ordens opostas para os FKs (um índice sendo o índice PK)
  3. Armazene o relacionamento de uma maneira com dois índices e permita que o segundo seja inserido de qualquer maneira (parece meio nojento, mas ei, completude)
  4. Crie algum tipo de tabela de agrupamento e tenha um FK para ela na tabela original. (Levanta muitas questões. A tabela de agrupamento teria apenas um número; por que ter a tabela? Tornar FK NULLable ou ter grupos com uma única linha associada?)

Quais são alguns dos principais prós e contras dessas formas e, claro, existe alguma maneira que eu não tenha pensado?

Aqui está um SQLFiddle para brincar: http://sqlfiddle.com/#!12/7ee1a/1/0 . (Acontece que é o PostgreSQL, já que é o que estou usando, mas não acho que essa pergunta seja muito específica do PostgreSQL.) Atualmente, ele armazena o relacionamento e seu reverso apenas como exemplo.

database-design foreign-key

1 respostas

  1. Best Answer

    O que você projetou é bom. O que precisa ser adicionado é uma restrição para tornar o relacionamento sem direção. Portanto, você não pode ter uma (1,5)linha sem que uma (5,1)linha seja adicionada também.

    Isso pode ser feito * com uma restrição de auto-referência na tabela de ponte.

    *: pode ser realizado em Postgres, Oracle, DB2 e todos os DBMS que implementaram restrições de chave estrangeira como descrito no padrão SQL (adiado, por exemplo, verificado no final da transação). Servidor que os verifica no final da instrução e esta construção ainda funciona. Você não pode fazer isso no MySQL porque "InnoDB verifica restrições UNIQUE e FOREIGN KEY linha por linha" .

    Portanto, no Postgres, o seguinte corresponderá aos seus requisitos:

    CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

    Testado em: SQL-Fiddle

    Se você tentar adicionar uma linha (1,5):

    INSERT INTO bridge_x VALUES
(1,5) ;

    Ele falha com:

    ERRO: inserir ou atualizar na tabela "bridge_x" viola a restrição de chave estrangeira "x_x_directionless"
    Detalhe: Chave (x_id2, x_id1)=(5, 1) não está presente na tabela "bridge_x".:
    INSERT INTO bridge_x VALUES (1,5)

    Além disso, você pode adicionar uma CHECKrestrição se quiser proibir (y,y)linhas:

    ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

    Existem outras maneiras de implementar isso como você mencionou, como armazenar apenas uma direção do relacionamento (em uma linha, não duas), forçando o id inferior x_id1e o id superior na x_id2coluna. Parece mais fácil de implementar, mas geralmente leva a consultas mais complexas posteriormente:

    CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);
    • 10

relate perguntas