Может ли один и тот же столбец иметь первичный ключ и ограничение внешнего ключа для другого столбца?
Table1: ID - Primary column, foreign key constraint for Table2 ID
Table2: ID - Primary column, Name
Будет ли это проблемой, если я попытаюсь удалить данные таблицы 1?
Delete from table1 where ID=1000;
Спасибо.
С этим не должно быть проблем. Рассмотрим следующий пример:
CREATE TABLE table2 (
id int PRIMARY KEY,
name varchar(20)
) ENGINE=INNODB;
CREATE TABLE table1 (
id int PRIMARY KEY,
t2_id int,
FOREIGN KEY (t2_id) REFERENCES table2 (id)
) ENGINE=INNODB;
INSERT INTO table2 VALUES (1, 'First Row');
INSERT INTO table2 VALUES (2, 'Second Row');
INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 1);
INSERT INTO table1 VALUES (3, 1);
INSERT INTO table1 VALUES (4, 2);
Теперь таблицы содержат:
SELECT * FROM table1;
+----+-------+
| id | t2_id |
+----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
+----+-------+
4 rows in set (0.00 sec)
SELECT * FROM table2;
+----+------------+
| id | name |
+----+------------+
| 1 | First Row |
| 2 | Second Row |
+----+------------+
2 rows in set (0.00 sec)
Теперь мы можем успешно удалить строки следующим образом:
DELETE FROM table1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
DELETE FROM table1 WHERE t2_id = 2;
Query OK, 1 row affected (0.00 sec)
Однако мы не сможем удалить следующее:
DELETE FROM table2 WHERE id = 1;
ERROR 1451 (23000): A foreign key constraint fails
Если бы мы определили внешний ключ для table1 с помощью CASCADE, мы могли бы удалить родителя, и все дочерние элементы были бы удалены автоматически:
CREATE TABLE table2 (
id int PRIMARY KEY,
name varchar(20)
) ENGINE=INNODB;
CREATE TABLE table1 (
id int PRIMARY KEY,
t2_id int,
FOREIGN KEY (t2_id) REFERENCES table2 (id) ON DELETE CASCADE
) ENGINE=INNODB;
INSERT INTO table2 VALUES (1, 'First Row');
INSERT INTO table2 VALUES (2, 'Second Row');
INSERT INTO table1 VALUES (1, 1);
INSERT INTO table1 VALUES (2, 1);
INSERT INTO table1 VALUES (3, 1);
INSERT INTO table1 VALUES (4, 2);
Если мы повторим предыдущую неудачную операцию DELETE, дочерние строки в table1 будут удалены так же, как и родительская строка в table2:
DELETE FROM table2 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
SELECT * FROM table1;
+----+-------+
| id | t2_id |
+----+-------+
| 4 | 2 |
+----+-------+
1 row in set (0.00 sec)
SELECT * FROM table2;
+----+------------+
| id | name |
+----+------------+
| 2 | Second Row |
+----+------------+
1 row in set (0.00 sec)
Назначение первичного ключа и внешнего ключа одному и тому же столбцу в таблице:
create table a1 (
id1 int not null primary key
);
insert into a1 values(1),(2),(3),(4);
create table a2 (
id1 int not null primary key foreign key references a1(id1)
);
insert into a2 values(1),(2),(3);
Да, оно может.
Нет, не будет.
P.S. Но вы не сможете удалить данные table2 без удаления соответствующих строк table1.
П.П.С. Я реализовал такую структуру в Postgres, но она должна быть аналогична для MySQL.
Ответ, предоставленный Джейсоном, возможно, когда-то работал, но когда я попытался использовать этот ответ в 2021 году на сервере MySQL 5.7, он жалуется. Синтаксис, который я использовал, чтобы заставить это работать, был;
CREATE TABLE a1 (
id1 INT NOT NULL PRIMARY KEY
);
INSERT INTO a1 VALUES (1),(2),(3),(4);
CREATE TABLE a2 (
id1 INT NOT NULL,
PRIMARY KEY (id1),
CONSTRAINT `fk_id1` FOREIGN KEY (id1) REFERENCES a1(id1)
);
INSERT INTO a2 VALUES (1),(2),(3);
Для взаимно-однозначных отношений этого типа я также настоятельно рекомендую создавать внешние ключи как;
CONSTRAINT `fk_id1` FOREIGN KEY (id1) REFERENCES a1(id1) ON DELETE CASCADE
