0

I am fairly new to the world of SqL , I am having this common problem that most people seem to have fix but I am not able to , when inserting data i am getting Error 1452 , i have try the following tread on here Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

each solution has failed here is my SqL script:

# Author: Waheed Rafiq
# Date: 02/04/2016
# testX database
# list to Create tables 

# tbl_Admin 
create table tbl_admin(
adminID smallint not null  primary key auto_increment,
employeeID smallint null ,
admin_employee_Fname varchar(20),
admin_employee_Sname varchar(20),

admin_accessRight enum ('standard','accounts','classic','admin')
); 
# add dummy data :
insert tbl_admin values(1,1,'Waheed','Rafiq','admin');
insert tbl_admin values(2,2,'Sarah','Rafiq','classic');
insert tbl_admin values(3,3,'Philps','Smiths','standard');
insert tbl_admin values(4,4,'John','Edwards','standard');
insert tbl_admin values(5,5,'Ash','Codi','accounts');
insert tbl_admin values(6,6,'Kath','Womi','admin');

# tbl_employee 
create table tbl_employee(
employeeID smallint not null  primary key auto_increment,
adminID smallint null,
employee_Fname varchar(20),
employee_Sname varchar(20),
employee_AddressL1 varchar(100),
employee_AddressL2 varchar(100),
employee_PostCode varchar(10),
employee_tel_type enum ('work','personal','other'),
employee_telephone varchar(30) ,
employee_JobRole varchar(20),
employee_Salary float

); 

# add dummpy data to employee table

 insert tbl_employee values(1,1,'Waheed','Rafiq','36 do thed        road','Alcala','2345','work','07525177630','Admin','60.568');
insert tbl_employee values(2,2,'Sarah','Rafiq','126 Cala de  Polo','Alcala','4345','personal','034525177630','Office Admin','1.568');
insert tbl_employee values(3,3,'Philps','smiths','367 fake de pounda','Alcala','345','work','09258177630','Maintance','15.568');
insert tbl_employee values(4,4,'John','Edwards','36 Greader los OSO','Madrid','1045','work','07525177888','office clark','2.568');
insert tbl_employee values(5,5,'Ash','Codi','88 do thed road','Alcala','2345','work','07528547858','Finance','30.568');
insert tbl_employee values(6,6,'Kath','Womi','555 Gotham road','Madrid','1111','work','07525177999','Admin Techi','40.568');

# check up to this point all works fine dummpy data is added.

now I add the FK keys : in a separate scripts

# Author: Waheed Rafiq
# Date: 02/04/2016
# testx database
# Add list of FK to database.

# FK to tbl_admin

alter table blindx_test1.tbl_admin add foreign key(employeeID) References tbl_employee(employeeID);

# add FK to tbl_admin 

alter table blindx_test1.tbl_employee add foreign key(adminID) references  tbl_admin(adminID);

this part works fine too and now here is the problem when inserting new data into the tables i am getting Error code 1452:

insert tbl_admin values( 7,7,'Samia','Fazal','admin');

insert tbl_employee values(7,7 ,'Samia','Fazal','786 Bromford    Lane','Madrid','1110','other','07525177999','Admin Techi','40.568');

any help or support , would be very much appreciated, something so simple is now turning out to be hard :( am sure its something obvious that you guys would be able to spot.

thanks for your support. will keep on searching for a fix if I do find one shall update my post.

Community
  • 1
  • 1
Waheed Rafiq
  • 460
  • 1
  • 6
  • 17
  • Always list the columns in an `insert` statement. You will probably then see what is causing the problem and be able to fix it easily. – Gordon Linoff Apr 02 '16 at 16:42

1 Answers1

3

For this query:

insert tbl_admin values( 7,7,'Samia','Fazal','admin');

Read the entire error:

Cannot add or update a child row: a foreign key constraint fails (db_9_3137a.tbl_admin, CONSTRAINT tbl_admin_ibfk_1 FOREIGN KEY (employeeID) REFERENCES tbl_employee (employeeID))

This is pretty clear. You are trying to insert a value of 7 in employeeID. There is no such value, so you get the error.

Here is a SQL Fiddle with the code up to the last two inserts.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the feedback Gordon , what I am misunderstanding is employeeID has value 7 and so does adminID in each table these are declare as PK and FK keys. so how do you insert a value in these fields? am a bit lost, in addtion i don't have a field call 'empid' did you mean employeeID? – Waheed Rafiq Apr 03 '16 at 09:25
  • @WaheedRafiq . . . There is no value "7" in either `tbl_admin` or `tbl_employees`. The reference table needs to contain these values. – Gordon Linoff Apr 04 '16 at 02:36