外键在mysql中不起作用 [英] foreign key does not working in mysql

问题描述

你好，大家好，



我的数据库出了问题。我正在尝试创建2个表，其中一个表有其他表的外键。我的问题是，我将其作为外键的数据不起作用，列文本字段保持为空。我试着改变表，删除表，甚至使用这个命令'set foreign_key_checks = 0';但是列文本字段仍为空。仅供参考，我已经手动尝试并使用脚本来做到这一点但却会出现相同的结果。我正在使用mysql workbench 6.1.6。有人可以帮助我，因为我是mysql的新手，谢谢。



示例代码我做过：

Hello and good day everyone,

i got a problem in my database. i'm trying to create 2 table and one of these table have foreign key to other table. my problem is, the data that i make it as foreign key does not working, and the column text field remain null. i have try alter the table, drop the table and even using this command 'set foreign_key_checks=0'; but still the column text field remain null. FYI, i have try manually and use script to do that and yet the same result occur. i'm using mysql workbench 6.1.6.Could someone help me since i'm newbie in mysql,thank you.

sample code i've done:

create table if not exists Tenant_dummydata.Zone_details(
Zone_id int not null auto_increment;
Zone_name varchar(10),
primary key(Zone_id));

insert into Zone_details(Zone_name) values ('A');
insert into Zone_details(Zone_name) values ('B');
insert into Zone_details(Zone_name) values ('C');
insert into Zone_details(Zone_name) values ('D');
insert into Zone_details(Zone_name) values ('E');

create table if not exists Tenant_dummydata.Lot_details(
Lot_id int not null auto_increment,
Lot_size bigint,
Zone_id int,
primary key(Lot_id),
CONSTRAINT Zone_id foreign key(Zone_id) 
	references Zone_details(Zone_id));

insert into Lot_details(Lot_size) values ('11450');
insert into Lot_details(Lot_size) values ('23890');
insert into Lot_details(Lot_size) values ('35000');
insert into Lot_details(Lot_size) values('38450');
insert into Lot_details(Lot_size) values ('49800');

推荐答案

您必须从zone_details插入有效的外键值（zone_id），否则2个表之间的链接会被破坏，例如：

You must insert a valid foreign key value (zone_id) from zone_details, otherwise the link between the 2 tables is broken, for example:

insert into Lot_details(Lot_size, zone_id) values ('11450', (select zone_id from zone_details where zone_name='A'));

参考： sql_foreignkey [ ^ ]

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