MySQL 同一列可以对另一列有主键和外键约束吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3774923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can the same column have primary key & foreign key constraint to another column
提问by Sharpeye500
Can the same column have primary key & foreign key constraint to another column?
同一列可以对另一列具有主键和外键约束吗？
Table1: ID - Primary column, foreign key constraint for Table2 ID
Table2: ID - Primary column, Name
Will this be an issue if i try to delete table1 data?
如果我尝试删除 table1 数据，这会是一个问题吗？
Delete from table1 where ID=1000;
Thanks.
谢谢。
回答by Daniel Vassallo
There should be no problem with that. Consider the following example:
应该没有问题。考虑以下示例：
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);
The tables now contain:
这些表现在包含：
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)
Now we can successfully delete rows like this:
现在我们可以像这样成功删除行：
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)
However we won't be able to delete the following:
但是，我们将无法删除以下内容：
DELETE FROM table2 WHERE id = 1;
ERROR 1451 (23000): A foreign key constraint fails
If we had defined the foreign key on table1with the CASCADEoption, we would have been able to delete the parent, and all the children would get deleted automatically:
如果我们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);
If we were to repeat the previously failed DELETE, the children rows in table1will be deleted as well as the parent row in table2:
如果我们重复之前的失败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)
回答by Jason Clark
Assigning Primary Key And Foreign key to the same column in a Table:
将主键和外键分配给表中的同一列：
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);
回答by Innokenty
Yes, it can.
是的，它可以。
No, it won't.
不，不会。
P.S. But you'll not be able to delete table2 data without deleting corresponding table1 rows obviously.
PS但是如果不删除相应的table1行，您将无法删除table2数据。
P.P.S. I've implemented such structure in Postgres, but it must be similar for MySQL.
PPS 我已经在 Postgres 中实现了这样的结构，但它对于 MySQL 必须是类似的。
