同一列可以有主键 &对另一列的外键约束

本教程将介绍同一列可以有主键 &对另一列的外键约束的处理方法，这篇教程是从别的地方看到的，然后加了一些国外程序员的疑问与解答，希望能对你有所帮助，好了，下面开始学习吧。

问题描述

同一列可以有主键 &外键约束到另一列?

Table1: ID - Primary column, foreign key constraint for Table2 ID
Table2: ID - Primary column, Name 

如果我尝试删除 table1 数据，这会是一个问题吗?

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)

好了关于同一列可以有主键 &对另一列的外键约束的教程就到这里就结束了，希望趣模板源码网找到的这篇技术文章能帮助到大家，更多技术教程可以在站内搜索。