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

问题描述

我有一个新的蜜蜂我已经使用SQL Server 2000之前，我的问题是当在SQL Server 2000中创建两个表说，位置和项目表项目有一个外键引用位置表时插入值的位置项目也更新是不需要在项目表中的外键插入相似的值为什么是不可能在MySQL中，当我插入值的位置使用插入命令和当在项目上使用选择命令时，它不显示值在外键请检查下面的代码

  mysql>创建表位置（
  - > id int not null，
  - >主键（id））
  - >发动机= innodb的; 
 Query OK，0 rows affected（0.11 sec）
 
 
 mysql>创建表项目（
  - > id int，
  - > location_id int，
  - >外键（location_id）在del 
上引用位置（id） ete cascade）
  - >发动机= innodb的; 
 Query OK，0 rows affected（0.31 sec）
 
 
 mysql>插入位置值（'1'）
查询OK，1行受影响（0.34秒）
 
 mysql>从位置选择* 
 + ---- + 
 | id | 
 + ---- + 
 | 1 | 
 + ---- + 
 1行（0.00秒）
 
 mysql>从项目中选择* 
空集（0.00秒）
  

你可以在上面的代码中看到SQL SERVER 2000，该值反映在子表中为什么在MySql中是不可能的为什么应该在两个表中插入值是不是可能在MySql中，当我插入位置表的项目表中选择值自动和显示它在选择查询当我查询项目表

解决方案

从我能看到的例子中， t插入任何项目表中的级联更新或删除执行。

我建议作为一个更好的例子来演示你之后的行为执行以下步骤后，执行以上步骤：

  insert into projects values（1,1）; 
从项目中选择*; 
 update location set id = 2 where id = 1; 
从项目中选择*; 
  

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

b $ b

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)

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;

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屋！