Click here to Skip to main content
15,502,191 members
Please Sign up or sign in to vote.
2.00/5 (1 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:
SQL
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 18:01pm
v2

1 solution

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[^]
 
Share this answer
 
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900