Como implementar um relacionamento muitos-para-muitos no PostgreSQL?

Eu acredito que o título é auto-explicativo. Como você cria a estrutura de tabelas no PostgreSQL para criar um relacionamento muitos-para-muitos.

Meu exemplo:

Product(name, price); Bill(name, date, Products); 

As instruções SQL DDL (linguagem de definição de dados) poderiam ser assim:

 CREATE TABLE product ( product_id serial PRIMARY KEY -- implicit primary key constraint , product text NOT NULL , price numeric NOT NULL DEFAULT 0 ); CREATE TABLE bill ( bill_id serial PRIMARY KEY , bill text NOT NULL , billdate date NOT NULL DEFAULT CURRENT_DATE ); CREATE TABLE bill_product ( bill_id int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE , product_id int REFERENCES product (product_id) ON UPDATE CASCADE , amount numeric NOT NULL DEFAULT 1 , CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id) -- explicit pk ); 

Eu fiz alguns ajustes:

  • A relação n: m é normalmente implementada por uma tabela separada – bill_product , neste caso.

  • Eu adicionei colunas serial como chaves primárias substitutas . Eu recomendo isso, porque o nome de um produto não é exclusivo. Além disso, impor exclusividade e referenciar a coluna em foreign keys é muito mais barato com um integer de 4 bytes do que com uma string armazenada como text ou varchar .
    No Postgres 10 ou posterior, considere uma coluna IDENTITY lugar. Detalhes:

  • Não use nomes de tipos básicos de dados, como date como identificadores . Embora isso seja possível, é um estilo ruim e leva a erros confusos e mensagens de erro. Use identificadores legais, minúsculos e sem aspas . Nunca use palavras reservadas e evite identificadores de caso misto com aspas duplas, se puder.

  • name não é um bom nome. Eu renomei a coluna de name do product tabela como product . Essa é uma convenção de nomenclatura melhor. Caso contrário, quando você une algumas tabelas em uma consulta – que faz muito em um database relacional – você acaba com várias colunas nomeadas name e precisa usar aliases de coluna para resolver a confusão. Isso não ajuda. Outro anti-padrão comum seria apenas id como nome da coluna.
    Não tenho certeza qual seria o nome de uma bill . Talvez o bill_id possa ser o nome nesse caso.

  • price é do tipo de dados numeric para armazenar números fracionários precisamente como entrados (tipo de precisão arbitrária em vez de tipo de ponto flutuante). Se você lida exclusivamente com números inteiros, faça esse integer . Por exemplo, você pode economizar preços como Cents .

  • O amount ( "Products" na sua pergunta) vai para a tabela de links bill_product e também é do tipo numeric . Novamente, integer se você lida exclusivamente com números inteiros.

  • Você vê as foreign keys em bill_product ? Eu criei ambos para mudanças em cascata ( ON UPDATE CASCADE ): Se um product_id ou bill_id deve mudar, a mudança é em cascata para todas as inputs dependentes no bill_product e nada quebra.
    Também usei ON DELETE CASCADE para bill_id : Se você excluir uma conta, os detalhes serão excluídos com ela.
    Não é assim para produtos: você não deseja excluir um produto usado em uma fatura. O Postgres lançará um erro se você tentar isso. Você adicionaria outra coluna ao product para marcar linhas obsoletas.

  • Todas as colunas neste exemplo básico acabam sendo NOT NULL , portanto valores NULL não são permitidos. (Sim, todas as colunas – as colunas usadas em uma chave primária são definidas como UNIQUE NOT NULL automaticamente.) Isso porque os valores NULL não fazem sentido em nenhuma das colunas. Isso facilita a vida de um iniciante. Mas você não vai fugir tão facilmente, você precisa entender o manuseio NULL de qualquer maneira. Colunas adicionais podem permitir valores NULL , funções e junções podem introduzir valores NULL em consultas etc.

  • Leia o capítulo sobre CREATE TABLE no manual .

  • As chaves primárias são implementadas com um índice exclusivo nas colunas-chave, o que torna as consultas com condições nas colunas PK rápidas. No entanto, a sequência de colunas-chave é relevante em chaves de várias colunas. Como o PK em bill_product está bill_product (bill_id, product_id) no meu exemplo, convém adicionar outro índice apenas em product_id ou (product_id, bill_id) se você tiver consultas em busca de um product_id e nenhum bill_id . Detalhes:

    • Chave primária composta do PostgreSQL
    • Um índice composto também é bom para consultas no primeiro campo?
    • Trabalhando de índices no PostgreSQL
  • Leia o capítulo sobre índices no manual .