Implementação de um relacionamento muitos-para-muitos com restrições de participação total em SQL

Não é fácil de fazer em SQL, mas não é impossível. Se você deseja que isso seja aplicado apenas por meio de DDL, o DBMS deve ter DEFERRABLErestrições implementadas. Isso pode ser feito (e pode ser verificado para funcionar no Postgres, que os implementou):

-- lets create first the 2 tables, A and B:
CREATE TABLE a 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid) 
 );

CREATE TABLE b 
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid) 
 );

-- then table R:
CREATE TABLE r 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,  
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

Até aqui está o design "normal", onde todos Apodem ser relacionados a zero, um ou muitos Be todos Bpodem ser relacionados a zero, um ou muitos A.

A restrição de "participação total" precisa de restrições na ordem inversa (de Ae B, respectivamente, referenciando R). Ter FOREIGN KEYrestrições em direções opostas (de X para Y e de Y para X) é formar um círculo (um problema de "ovo e galinha") e é por isso que precisamos que pelo menos um deles seja DEFERRABLE. Neste caso, temos dois círculos ( A -> R -> Ae B -> R -> B, portanto, precisamos de duas restrições adiáveis:

-- then we add the 2 constraints that enforce the "total participation":
ALTER TABLE a
  ADD CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r 
    DEFERRABLE INITIALLY DEFERRED ;

ALTER TABLE b
  ADD CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r 
    DEFERRABLE INITIALLY DEFERRED ;

Então podemos testar se podemos inserir dados. Observe que o INITIALLY DEFERREDnão é necessário. Poderíamos ter definido as restrições como, DEFERRABLE INITIALLY IMMEDIATEmas então teríamos que usar a SET CONSTRAINTSinstrução para adiá-las durante a transação. Em todos os casos, porém, precisamos inserir nas tabelas em uma única transação:

-- insert data 
BEGIN TRANSACTION ;
    INSERT INTO a (aid, bid)
    VALUES
      (1, 1),    (2, 5),
      (3, 7),    (4, 1) ;

    INSERT INTO b (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7) ;

    INSERT INTO r (aid, bid)
    VALUES
      (1, 1),    (1, 2),
      (2, 3),    (2, 4),
      (2, 5),    (3, 6),
      (3, 7),    (4, 1),
      (4, 2),    (4, 7) ; 
 END ;

Testado no SQLfiddle .

Se o DBMS não tiver DEFERRABLErestrições, uma solução alternativa é definir as colunas A (bid)e como . Os procedimentos/declarações terão que primeiro inserir em e (colocando nulos em e respectivamente), depois inserir e atualizar os valores nulos acima para os valores não nulos relacionados de .B (aid)NULLINSERTABbidaidRR

Com essa abordagem, o DBMS não impõe os requisitos apenas por DDL, mas todos os procedimentos INSERT(e UPDATEe DELETEe MERGE) devem ser considerados e ajustados de acordo e os usuários devem ser restritos a usá-los apenas e não ter acesso de gravação direto às tabelas.

Ter círculos nas FOREIGN KEYrestrições não é considerado por muitos a melhor prática e por boas razões, sendo a complexidade uma delas. Com a segunda abordagem, por exemplo (com colunas anuláveis), a atualização e exclusão de linhas ainda terão que ser feitas com código extra, dependendo do DBMS. No SQL Server, por exemplo, você não pode simplesmente colocar ON DELETE CASCADEporque atualizações e exclusões em cascata não são permitidas quando há círculos FK.

Por favor, leia também as respostas nesta questão relacionada:
Como ter um relacionamento um-para-muitos com uma criança privilegiada?

Outra terceira abordagem (veja minha resposta na pergunta acima mencionada) é remover completamente os FKs circulares. Assim, mantendo a primeira parte do código (com tabelas A, B, Re chaves estrangeiras apenas de R para A e B) quase intacta (simplificando-a na verdade), adicionamos outra tabela para Aarmazenar o item relacionado "must have one" de B. Assim, a A (bid)coluna se move para A_one (bid)O mesmo é feito para a relação inversa de B para A:

CREATE TABLE a 
( aid INT NOT NULL,
  CONSTRAINT a_pk PRIMARY KEY (aid) 
 );

CREATE TABLE b 
( bid INT NOT NULL,
  CONSTRAINT b_pk PRIMARY KEY (bid) 
 );

-- then table R:
CREATE TABLE r 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT r_pk PRIMARY KEY (aid, bid),
  CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,  
  CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
 );

CREATE TABLE a_one 
( aid INT NOT NULL,
  bid INT NOT NULL,
  CONSTRAINT a_one_pk PRIMARY KEY (aid),
  CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

CREATE TABLE b_one
( bid INT NOT NULL,
  aid INT NOT NULL,
  CONSTRAINT b_one_pk PRIMARY KEY (bid),
  CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
 );

A diferença entre a 1ª e a 2ª abordagem é que não há FKs circulares, portanto, atualizações e exclusões em cascata funcionarão bem. A aplicação da "participação total" não é apenas por DDL, como na 2ª abordagem, e deve ser feita por procedimentos apropriados ( INSERT/UPDATE/DELETE/MERGE). Uma pequena diferença com a segunda abordagem é que todas as colunas podem ser definidas como não anuláveis.

Outra quarta abordagem (consulte a resposta de @Aaron Bertrand na pergunta acima mencionada) é usar índices exclusivos filtrados/parciais , se estiverem disponíveis em seu DBMS (você precisaria de dois deles, na Rtabela, para este caso). Isso é muito semelhante à 3ª abordagem, exceto que você não precisará das 2 tabelas extras. A restrição de "participação total" ainda precisa ser aplicada por código.

Você não pode diretamente. Para começar, você não seria capaz de inserir o registro para A sem um B já existente, mas não poderia criar o registro B se não houvesse um registro A para ele. Existem várias maneiras de aplicá-lo usando coisas como gatilhos - você teria que verificar em cada inserção e exclusão se pelo menos um registro correspondente permanece na tabela de links AB.