Click here to Skip to main content
14,644,614 members
Rate this:
Please Sign up or sign in to vote.
See more:
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');
Posted
Updated 9-Jun-14 17:01pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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'));

Refer: sql_foreignkey[^]
   
v2
Comments
_nurhan 9-Jun-14 23:57pm
   
oh really? tqvm, it's solved! i have seen the link 'w3choll', but it's only show the query for FK, and i dont know what else to do beside that.
Peter Leow 9-Jun-14 23:58pm
   
You are welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100