Skip navigation links

MySQL Forums :: Newbie :: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint


Advanced Search

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


Subject Written By Posted
PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/05/2011 10:34PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Peter Brawley 04/05/2011 10:40PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/05/2011 10:42PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/05/2011 11:13PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Peter Brawley 04/05/2011 11:20PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/05/2011 11:33PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Peter Brawley 04/06/2011 08:42AM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/06/2011 01:34PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/06/2011 01:37PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/06/2011 02:15PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint francisco gomez 04/20/2011 11:20PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Peter Brawley 04/06/2011 02:12PM
Re: PLS HELP: ERROR 1452 (23000) Foreign Key Constraint Roland Chan 04/06/2011 02:16PM


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.