PLS HELP: ERROR 1452 (23000) Foreign Key Constraint

[Roland Chan]

Hi all,

MySQL select version() is 5.5.9.

When I execute the sql script below, I encounter a very perplexing foreign key constraint error. Can anyone spot what it is we're doing wrong?

To reproduce:

1. create database constraint_test;
2. create the constraint_test.sql file and paste the sql below.
3. from the cmd line execute "mysql constraint_test < constraint_test.sql"

As you'll see, the foreign key constraint error we consistently receive is similar to:

"ERROR 1452 (23000) at line 55: Cannot add or update a child row: a foreign key constraint fails (`constraint_test`.`beneficiary`, CONSTRAINT `FK41BADEC55CE3480` FOREIGN KEY (`insured_id`) REFERENCES `Insured` (`insured_id`))"

However, there is definitely an insured_id in the parent table Insured.

Please help if you can!

constraint_sql contents:

create table Beneficiary (
beneficiary_id bigint not null,
district varchar(255),
serviceUnit varchar(255),
insuredNo integer,
beneficiaryIndex integer,
relationship varchar(255),
percentage double precision,
fullName varchar(255),
lastUpdatedDate datetime,
insured_id bigint,
contractNo varchar(255),
primary key (beneficiary_id)
);

create table Client (
client_id bigint not null,
firstName varchar(255),
lastName varchar(255),
email varchar(255),
initial varchar(255),
birthDate datetime,
district varchar(255),
serviceUnit varchar(255),
genderType varchar(255),
externalId varchar(255),
externalTempId varchar(255),
taxationProvince varchar(255),
children varchar(255),
manufacturerClientNumber varchar(255),
primary key (client_id)
);

create table Insured (
insured_id bigint not null,
client_id bigint not null,
insuredNo integer,
primary key (insured_id)
);

alter table Beneficiary
add index FK41BADEC55CE3480 (insured_id),
add constraint FK41BADEC55CE3480
foreign key (insured_id)
references Insured (insured_id);

alter table Insured
add index FKD7E770CAC207FE14 (client_id),
add constraint FKD7E770CAC207FE14
foreign key (client_id)
references Client (client_id);

insert into Client (client_id) values (1);
insert into Insured (insured_id, client_id ) values (1,1);
insert into Beneficiary (beneficiary_id, insured_id) values (1,1);