Início / dba / Perguntas / 339907
Accepted
FloT
Asked: 2024-05-30 21:34:59 +0800 CST

Por que o MySQL gera uma instrução `key` em DDL para chaves primárias compostas?

  • 772

Estou usando o MySQL versão 8.3.0.

Posso criar um relacionamento “muitos para muitos” entre tabelas de pessoas e equipes com as seguintes instruções SQL:

create table persons (
  id bigint not null,
  last_name varchar(255) not null,
  first_name varchar(255),
  primary key (id)
);

create table teams (
  id bigint not null,
  team_name varchar(255) not null,
  primary key (id)
);

create table person_team (
  person_id bigint not null,
  team_id bigint not null,
  primary key (person_id, team_id),
  constraint fk_person_team_person foreign key (person_id) references persons(id),
  constraint fk_person_team_team foreign key (team_id) references teams(id)
);

Portanto, a person_teamtabela possui uma chave primária composta e cada uma de suas colunas é uma chave estrangeira para as tabelas às quais ela une.

Se eu recuperar a instrução DDL para person_teamwith show create table person_team;, isso me dará o seguinte SQL:

CREATE TABLE `person_team` (
  `person_id` bigint NOT NULL,
  `team_id` bigint NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`),
  KEY `fk_person_team_team` (`team_id`),
  CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`),
  CONSTRAINT `fk_person_team_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Assim, ele adiciona automaticamente uma KEYinstrução (linha 5) com o nome chave estrangeira à teamstabela.

Por que esse comportamento? Esta KEYinstrução extra não parece necessária para mim. Por que mostra uma chave para fk_person_team_teame não para fk_person_team_person? A afirmação inicial está errada ou incompleta? O DDL gerado automaticamente está correto? Quando despejo meu banco de dados com mysqldump, obtenho o mesmo código do DDL gerado automaticamente. É mais seguro modificar este código para restaurar o banco de dados?

mysql

2 respostas

  1. Best Answer

    A resposta, como às vezes acontece, pode ser encontrada na documentação :

    O MySQL requer índices em chaves estrangeiras e chaves referenciadas para que as verificações de chaves estrangeiras possam ser rápidas e não exijam uma varredura de tabela. Na tabela de referência, deve haver um índice onde as colunas da chave estrangeira sejam listadas como as primeiras colunas na mesma ordem. Esse índice é criado automaticamente na tabela de referência, caso não exista.

    (ênfase minha).

    Você tem duas colunas de referência em person_team: person_ide team_id; o primeiro é coberto pela chave primária da tabela, mas o último não, porque não está na primeira posição no PK, então um índice extra é criado para ele.

    • 7

  2. A razão é esta: a referência de chave estrangeira fk_person_team_personjá viu um índice que pode satisfazer o relacionamento de chave estrangeira. No seu caso, era a chave primária, pois a coluna principal é person_id.

    Quanto à outra referência de chave estrangeira, fk_person_team_teamnão havia nenhum índice presente que pudesse satisfazer o relacionamento de chave estrangeira.

    Vamos provar isso com o seguinte código

    DROP DATABASE IF EXISTS FKTEST;
CREATE DATABASE FKTEST;
USE FKTEST
create table persons (
  id bigint not null,
  last_name varchar(255) not null,
  first_name varchar(255),
  primary key (id)
);

create table teams (
  id bigint not null,
  team_name varchar(255) not null,
  primary key (id)
);

CREATE TABLE `person_team` (
  `person_id` bigint NOT NULL,
  `team_id` bigint NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`)
) ENGINE=InnoDB;


ALTER TABLE `person_team`
ADD FOREIGN KEY `fk_person_team_person` (`person_id`) REFERENCES `persons` (`id`);
SHOW CREATE TABLE person_team\G
ALTER TABLE `person_team`
ADD FOREIGN KEY `fk_person_team_team`   (`team_id`) REFERENCES `teams` (`id`);
SHOW CREATE TABLE person_team\G

    Isso criará a tabela person_team sem os FKs inicialmente. Vou então adicionar o FK um de cada vez

    Aqui está o resultado

    MariaDB [FKTEST]> DROP DATABASE IF EXISTS FKTEST;
ATE DATABASE FKTEST;
USE FKTEST
Query OK, 3 rows affected (0.035 sec)

MariaDB [(none)]> CREATE DATABASE FKTEST;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE FKTEST
Database changed
MariaDB [FKTEST]> create table persons (
    ->   id bigint not null,
last_na    ->   last_name varchar(255) not null,
    ->   first_name varchar(255),
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.009 sec)

MariaDB [FKTEST]> create table teams (
    ->   id bigint not null,
    ->   team_name varchar(255) not null,
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.014 sec)

MariaDB [FKTEST]> CREATE TABLE `person_team` (
    ->   `person_id` bigint NOT NULL,
    ->   `team_id` bigint NOT NULL,
    ->   PRIMARY KEY (`person_id`,`team_id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.011 sec)

MariaDB [FKTEST]> ALTER TABLE `person_team` ADD FOREIGN KEY `fk_person_team_person` (`person_id`) REFERENCES `persons` (`id`);
Query OK, 0 rows affected (0.034 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [FKTEST]> SHOW CREATE TABLE person_team\G
*************************** 1. row ***************************
       Table: person_team
Create Table: CREATE TABLE `person_team` (
  `person_id` bigint(20) NOT NULL,
  `team_id` bigint(20) NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`),
  CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.002 sec)

MariaDB [FKTEST]> ALTER TABLE `person_team` ADD FOREIGN KEY `fk_person_team_team`   (`team_id`) REFERENCES `teams` (`id`);
Query OK, 0 rows affected (0.036 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [FKTEST]> SHOW CREATE TABLE person_team\G
*************************** 1. row ***************************
       Table: person_team
Create Table: CREATE TABLE `person_team` (
  `person_id` bigint(20) NOT NULL,
  `team_id` bigint(20) NOT NULL,
  PRIMARY KEY (`person_id`,`team_id`),
  KEY `fk_person_team_team` (`team_id`),
  CONSTRAINT `fk_person_team_person` FOREIGN KEY (`person_id`) REFERENCES `persons` (`id`),
  CONSTRAINT `fk_person_team_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.013 sec)

MariaDB [FKTEST]>

    Por favor observe o seguinte

    • A primeira restrição FK foi adicionada, mas não criou um índice adicional
    • A segunda restrição FK foi adicionada e criou um índice adicional
    • 3

relate perguntas