[SOLVED] Foreign key constraint failure

[harkly]

I added a Foreign Key to a table an am trying to import data thru phpmyadmin

 

this is the error

 

#1452 - Cannot add or update a child row: a foreign key constraint fails (`artdb/image`, CONSTRAINT `image_ibfk_1` FOREIGN KEY (`artid`) REFERENCES `artist` (`artid`))

 

these are my tables :

 

CREATE TABLE artist (artid varchar(11) NOT NULL PRIMARY KEY,

fullName tinytext Not Null,
firstName tinytext NULL,
middleName tinytext NULL,
lastName tinytext NULL,
otherName tinytext NULL,
called tinytext NULL,
dob_xt tinytext NULL,
dob tinytext NULL,
dod_xt tinytext NULL,
dod tinytext,
nationality tinytext,
artform tinytext,
movid_1 varchar(7),
movid_2 varchar(7),
movid_3 varchar(7),
medid_1 varchar(7),
medid_2 varchar(7),
medid_3 varchar(7),
genid_1 varchar(7),
genid_2 varchar(7),
genid_3 varchar(7),
perid varchar(7),
bio mediumtext,
keywords tinytext,
notes text,
verified BOOL);



CREATE TABLE image (imgid varchar(11) NOT NULL PRIMARY KEY,
artid varchar(11) NOT NUll,
title tinytext,
datext_1 tinytext,
date tinytext,
medid varchar(7),
size_inch tinytext,
size_cm tinytext,
musid varchar(7),
movid varchar(7),
genid varchar(7),
perid varchar(7),
bio text,
keywords text,
notes text,
verified BOOL,
FOREIGN KEY (artid) REFERENCES artist (artid)
);

 

I have verified that there are no empty cells in the image table but there are a bunch of repeats, could that be an issue? I am thinking not. There isn't much info on the web for this. I understand a bit of the error but not this part `image_ibfk_1`  so I can't move on.

 

 

 

[Barand]

Sounds like you are trying to insert an image record for artist with id = X when there is not an artist with that id in the artist table

[Mchl]

You should take care in which order you're inserting your tables (and data). If you insert data that references to rows in other table, while the other table is still empty you will obviously get errors.

 

I found it useful to remove foreign keys before importing data, and then recreate them.

 

 

[harkly]

Its definitely not that I am importing an image file that has no artist record. Verified this multiply times.

 

I also tried the import first then create FK but got the same error when creating the FK.

 

Can you tell me why I would need a Foreing Key? In very simple terms. I have read a lot about them but I don't really think I need it if each table has a column conntecting each other.

[Barand]

A foreign key constraint enforces integrity of the data and serves two purposes

 

1 ) it stops you inserting a child record (image) that has no parent (artist)

 

2 ) it stops you deleting a parent if that parent has children (unless you have cascaded deletes, in which case deleting the parent also deletes the child records)

[harkly]

Thanks!!!