同一列可以有主键&外键约束到另一列 [英] Can the same column have primary key & foreign key constraint to another column

问题描述

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

 表1：ID  - 主键列，表2的外键约束ID 
表2： ID  - 主列，名称
  

如果我尝试删除table1数据，这是否会成为问题？ p>

 从table1中删除ID = 1000; 
  

谢谢。

解决方案

这应该没有问题。考虑下面的例子：

pre code $ 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 ;

插入到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行（0.00秒）
 
 SELECT * FROM table2; 
 + ---- + ------------ + 
 | id |名字| 
 + ---- + ------------ + 
 | 1 |第一行| 
 | 2 |第二行| 
 + ---- + ------------ + 
 2行（0.00秒）
  

$ b $ p现在我们可以成功删除这样的行了：
$ b $ pre $ 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）：外键约束失败
  

如果我们定义了外键在 table1 上键入 CASCADE 选项，我们可以删除父母，所有的孩子都会自动删除：

（

$ b $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' ;

CREATE TABLE table1（
id int PRIMARY KEY，
t2_id int，
FOREIGN KEY（t2_id）REFERENCES table2（id）ON DELETE CASCADE
） ENGINE = INNODB;

插入到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; 
查询OK，1行受影响（0.00秒）
 
 SELECT * FROM table1; 
 + ---- + ------- + 
 | id | t2_id | 
 + ---- + ------- + 
 | 4 | 2 | 
 + ---- + ------- + 
 1行（0.00秒）
 
 SELECT * FROM table2; 
 + ---- + ------------ + 
 | id |名字| 
 + ---- + ------------ + 
 | 2 |第二行| 
 + ---- + ------------ + 
 1行（0.00秒）
  

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?

Delete from table1 where ID=1000;

Thanks.

解决方案

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 table1 with the CASCADE option, we would have been able to delete the parent, and all the children would get deleted automatically:

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 table1 will be deleted as well as the parent row in 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)

这篇关于同一列可以有主键&外键约束到另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！