MySQL 外键引用 [英] MySQL Foreign Key Referencing

问题描述

我是新蜜蜂，我之前使用过 sql server 2000，我的问题是在 sql server 2000 中创建两个表时说 location 和 projects 表项目具有在 location 中插入值时引用 location 表的外键，项目也被更新了不需要在项目表中的外键中插入类似的值为什么当我使用插入命令在位置中插入值并且在项目上使用选择命令时它不显示外键中的值时在 mysql 中不可能请检查下面的代码

I m a new bee i have used sql server 2000 before my question is when creating two tables in sql server 2000 say location and projects table projects having a foreign key referencing the location table when inserting values in location the projects is also updated thats is no need to insert the similar value in the foreign key in projects table why is it not possible in mysql when i insert values in location using insert command and when using select command on projects it does not shows the value in foreign key please check the below code

mysql> create table location(
    -> id int not null,
    -> primary key(id))
    -> engine=innodb;
Query OK, 0 rows affected (0.11 sec)


mysql> create table projects(
    -> id int,
    -> location_id int,
    -> foreign key(location_id) references location(id) on update cascade on del
ete cascade)
    -> engine=innodb;
Query OK, 0 rows affected (0.31 sec)


mysql> insert into location values('1')
Query OK, 1 row affected (0.34 sec)

mysql> select * from location;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select * from projects;
Empty set (0.00 sec)

正如您在上面的代码中看到的那样，在 sql server 2000 中，值可能反映在子表中 为什么在 MySql 中不可能当我在位置表中插入时，项目表会自动选择值并在我查询项目表时在选择查询中显示它

as u see in the above code it was possible in sql server 2000 that the value was reflected in the child table why is it not possible in MySql Why should the value be insert in both the tables isn't it possible in MySql that when i insert in the location table the projects table pick the value automatically and show it in select Query when i query the projects table

推荐答案

从示例中可以看出，您没有在项目表中插入任何要执行级联更新或删除的内容.

From what I can see in the example, you haven't inserted anything in the projects table in which to have a cascaded update or delete performed.

我建议作为一个更好的例子来展示您在执行上述步骤后执行以下操作后的行为:

I would suggest as a better example to demonstrate the behaviour you're after that you perform the following, after doing the steps above:

insert into projects values (1,1);
select * from projects;
update location set id = 2 where id = 1;
select * from projects;

您最终应该看到的是，最初插入的项目行中的 location_id 将等于 1，然后在更新位置后，项目中的 location_id 应该更改为 2.这表明对 id 的更改location 表的 已级联以更新 projects 表中行的 location_id 字段.

What you should end up seeing, is that initially the location_id in the inserted projects row will be equal to 1, then after the update of location, the location_id in projects should change to 2. This demonstrates that the change to the id of the location table has cascaded to update the location_id field of the row in the projects table.

这篇关于MySQL 外键引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！