Thread: Foreign Key

Foreign Key

From

"Bob Pawley"

Date:

22 April 2010, 22:25:32

I don't understand what the following error means.

I am trying to add a foreign key to table image, that holds column device_id. This is to be controlled by column devices_id in table devices.

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;

ERROR: insert or update on table "image" violates foreign key constraint "deviceidfk"
DETAIL: Key (device_id)=(4022) is not present in table "devices".

Help appreciated.

Bob

Re: Foreign Key

From

Szymon Guz

Date:

22 April 2010, 22:32:56

2010/4/23 Bob Pawley <rjpawley@shaw.ca>

Hi

I don't understand what the following error means.

I am trying to add a foreign key to table image, that holds column device_id. This is to be controlled by column devices_id in table devices.

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;

ERROR: insert or update on table "image" violates foreign key constraint "deviceidfk"
DETAIL: Key (device_id)=(4022) is not present in table "devices".

Help appreciated.

Bob

Hi,

the error means that you try to insert some value to the column device_id in the image table that doesn't have a corresponding value in the devices table. If you want to insert something like this:

INSERT INTO image(device_id) VALUES ( 4022 );

there has to exist a record in the devices table that has the value 4022 in the corresponding column.

Oh, btw, the SQL command that you've shown doesn't add the foreign key on the image table.

regards

Szymon Guz