As chaves primárias compostas são uma prática ruim?

Dizer que o uso de "Composite keys as PRIMARY KEY is bad practice"é um absurdo total!

Os compostos PRIMARY KEYsão muitas vezes uma "coisa boa" e a única maneira de modelar situações naturais que ocorrem na vida cotidiana! Dito isto, também haveria muitas situações em que o uso de um PK composto seria complicado e pesado e, portanto, não seria a escolha ideal.

Sua pergunta é: "if composite primary keys are bad practice...(respondida) and if not, in which scenarios is their use beneficial?".

Abaixo está um exemplo de onde as chaves compostas representam uma escolha racional/benéfica como o PK (na verdade, o único racional como eu vejo - no violino aqui , há um exemplo extra de ter notas também!

No lado positivo das chaves compostas, pense no clássico exemplo de ensino Databases-101 de alunos e cursos e nos muitos cursos realizados por muitos alunos!

Criar tabelas curso e aluno:

CREATE TABLE course
(
  course_id SERIAL,
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);


CREATE TABLE student
(
  student_id SERIAL,
  student_name VARCHAR (50),
  CONSTRAINT student_pk PRIMARY KEY (student_id)
);

Vou lhe dar o exemplo no dialeto PostgreSQL (e MySQL ) - deve funcionar para qualquer servidor com um pouco de ajustes.

Agora, você obviamente quer acompanhar qual aluno está fazendo qual curso - então você tem o que é chamado de joining table(também chamado linkingde bridging, many-to-manyou m-to-ntabelas). Eles também são conhecidos como associative entitiesem jargão mais técnico!

1 curso pode ter muitos alunos.
1 aluno pode fazer vários cursos.

Então, você cria uma tabela de junção

CREATE TABLE registration
(
  cs_course_id INTEGER NOT NULL,
  cs_student_id INTEGER NOT NULL,

  -- now for FK constraints - have to ensure that the student
  -- actually exists, ditto for the course.

  CREATE CONSTRAINT cs_course_fk  FOREIGN KEY (cs_course_id)
    REFERENCES course  (course_id),
  CREATE CONSTRAINT cs_student_fk FOREIGN KEY (cs_student_id) 
    REFERENCES student (student_id)
);

Agora, a única maneira de dar sensatamente à registrationmesa PRIMARY KEYé fazer disso KEYuma combinação de curso e aluno. Dessa forma, você não pode obter:

• uma duplicata da combinação de aluno e curso

• um curso só pode ter o mesmo aluno matriculado uma vez, e

• um aluno só pode se matricular no mesmo curso uma única vez

• você também tem uma pesquisa pronta KEYno curso por aluno - AKA um índice de cobertura ,

• é trivial encontrar cursos sem alunos e alunos que não estão fazendo cursos!

  -- O exemplo db-fiddle tem a restrição PK dobrada no CREATE TABLE -- Pode ser feito de qualquer maneira. Eu prefiro ter tudo na CREATE TABLEdeclaração.

ALTER TABLE registration
ADD CONSTRAINT registration_pk 
PRIMARY KEY (cs_course_id, cs_student_id);

Agora, você poderia, se estivesse achando que as buscas por aluno por curso eram lentas, usar um UNIQUE INDEXon (sc_student_id, sc_course_id).

ALTER TABLE registration 
ADD CONSTRAINT course_student_sc_uq  
UNIQUE (cs_student_id, cs_course_id);

Não existe uma bala de prata para adicionar índices - eles tornarãoINSERT s e s mais UPDATElentos, mas com o grande benefício de diminuirSELECT bastante os tempos! Cabe ao desenvolvedor decidir indexar com base em seu conhecimento e experiência, mas dizer que PRIMARY KEYs compostos são sempre ruins é simplesmente errado.

No caso de juntar mesas, geralmente são as únicas PRIMARY KEY que fazem sentido! Juntar tabelas também é frequentemente a única maneira de modelar o que acontece nos negócios ou na natureza ou em praticamente todas as esferas que eu possa imaginar!

Este PK também é útil como um covering indexque pode ajudar a acelerar as pesquisas. Nesse caso, seria particularmente útil pesquisar regularmente em (course_id, student_id) o que, pode-se imaginar, muitas vezes pode ser o caso!

Este é apenas um pequeno exemplo de onde uma composição PRIMARY KEYpode ser uma ideia muito boa e a única maneira sensata de modelar a realidade! De cabeça, consigo pensar em muitos, muitos mais.

Um exemplo do meu próprio trabalho!

Considere uma tabela de voo contendo um flight_id, uma lista de aeroportos de partida e chegada e os horários relevantes e também uma tabela cabin_crew com membros da tripulação!

A única maneira sensata de modelar isso é ter uma tabela flight_crew com o flight_id e o crew_id como atributos e a única maneira sensata PRIMARY KEYé usar a chave composta dos dois campos!

Minha opinião semi-educada: uma "chave primária" não precisa ser a única chave exclusiva usada para pesquisar dados na tabela, embora as ferramentas de gerenciamento de dados a ofereçam como seleção padrão. Portanto, para escolher se deseja ter um composto de duas colunas ou um número gerado aleatoriamente (provavelmente em série) como a chave da tabela, você pode ter duas chaves diferentes ao mesmo tempo.

Se os valores de dados incluírem um termo exclusivo adequado que possa representar a linha, prefiro declará-lo como "chave primária", mesmo que composto, do que usar uma chave "sintética". A chave sintética pode ter um desempenho melhor por motivos técnicos, mas minha própria escolha padrão é designar e usar o termo real como chave primária, a menos que você realmente precise seguir outro caminho para fazer seu serviço funcionar.

Um Microsoft SQL Server tem o recurso distinto, mas relacionado, do "índice clusterizado" que controla o armazenamento físico de dados em ordem de índice e também é usado dentro de outros índices. Por padrão, uma chave primária é criada como um índice clusterizado, mas você pode escolher não clusterizado, de preferência depois de criar o índice clusterizado. Assim, você pode ter uma coluna gerada por identidade inteira como índice clusterizado e, digamos, o nome do arquivo nvarchar (128 caracteres) como chave primária. Isso pode ser melhor porque a chave de índice clusterizado é estreita, mesmo se você armazenar o nome do arquivo como o termo da chave estrangeira em outras tabelas - embora este exemplo seja um bom caso para não fazer isso.

Se o seu design envolve a importação de tabelas de dados que incluem uma chave primária inconveniente para identificar dados relacionados, você está praticamente preso a isso.

https://www.techopedia.com/definition/5547/primary-key descreve um exemplo de escolha entre armazenar dados com o número de segurança social de um cliente como chave do cliente em todas as tabelas de dados ou gerar um customer_id arbitrário quando você registrá-los. Na verdade, isso é um grave abuso do SSN, independentemente de funcionar ou não; é um valor de dados pessoais e confidenciais.

Portanto, uma vantagem de usar um fato do mundo real como chave é que, sem voltar à tabela "Cliente", você pode recuperar informações sobre eles em outras tabelas - mas também é um problema de segurança de dados.

Além disso, você está com problemas se o SSN ou outra chave de dados foi gravada incorretamente, então você tem o valor errado em 20 tabelas restritas em vez de apenas em "Cliente". Considerando que o customer_id sintético não tem significado externo, então não pode ser um valor errado.

Para expandir a resposta que @Vérace - Слава Україні deu. Chaves compostas também são necessárias quando você deseja ir mais fundo do que 2 níveis. Se continuarmos com o exemplo dado, cada curso provavelmente terá tarefas.

Create Table Assignment {
assignmentid int not null,
assignment title varchar(255) not null,
assignment details text null,
deadline datetime null,
constraint pk_assignment Primary Key (assignmentid)
}

No entanto, isso não funciona sem o contexto dos cursos, portanto, podemos adicionar um ID do curso à tabela

Alter Table Assignment add courseid int not null

Mas agora você quer poder ver os envios do aluno. Isso significa que precisamos de uma tabela vinculada ao Registro para as atribuições.

CREATE TABLE student_assignment
(
  cs_course_id INTEGER NOT NULL,
  cs_student_id INTEGER NOT NULL,
  cs_assignment_id integer not null,
  grade varchar(32) null,
  assignment blob null,
  submittedtime datetime null,
  Constraint pk_student_assignment primary key (cs_course_id, cs_student_id, cs_assignment_id)
}

Como você pode ver, a chave natural desta tabela são todas as três colunas. Você poderia reduzi-lo para duas colunas usando apenas o ID da atribuição e o ID do aluno, devido ao fato de que o ID do curso já existe na tabela de atribuição. No entanto, fazer isso torna mais difícil fazer relatórios que façam sentido. Por exemplo, quantos alunos enviaram trabalhos atrasados ​​para um curso, qual foi a distribuição das notas, etc.