MySQL Forums
Forum List  »  Newbie

PLS HELP: ERROR 1452 (23000) Foreign Key Constraint
Posted by: Roland Chan
Date: April 05, 2011 10:34PM

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

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.