MySQL Foreign Key: Cannot add or update a child row: a foreign key constraint fails

Question

I've created several tables, one being the parent table and three children tables.

CREATE TABLE `members` (
 `memberID` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(255) NOT NULL,
 `password` varchar(60) NOT NULL,
 PRIMARY KEY (`memberID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

CREATE TABLE `pets` (
 `petsID` int(11) NOT NULL AUTO_INCREMENT,
 `Gender` varchar(25) NOT NULL,
 `Age` varchar(25) NOT NULL,
 `Size` varchar(25) NOT NULL,
 `memberID` int(11) NOT NULL,
 PRIMARY KEY (`petsID`),
 KEY `fk_pets` (`memberID`),
 CONSTRAINT `pets_ibfk_1` FOREIGN KEY (`memberID`) REFERENCES `members` (`memberID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `owner` (
 `ownerID` int(11) NOT NULL AUTO_INCREMENT,
 `nameOwner` varchar(25) NOT NULL,
 `ageOwner` varchar(25) NOT NULL,
 `bioOwner` varchar(800) NOT NULL,
 `memberID` int(11) NOT NULL,
 PRIMARY KEY (`ownerID`),
 KEY `fk_owner` (`memberID`),
 CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`memberID`) REFERENCES `members` (`memberID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

CREATE TABLE `service` (
 `serviceID` int(11) NOT NULL AUTO_INCREMENT,
 `nameService` varchar(25) NOT NULL,
 `webService` varchar(25) NOT NULL,
 `memberID` int(11) NOT NULL,
 PRIMARY KEY (`serviceID`),
 KEY `fk_service` (`memberID`),
 CONSTRAINT `service_ibfk_1` FOREIGN KEY (`memberID`) REFERENCES `members` (`memberID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

After testing out my table I've gotten this error:

INSERT INTO owner (nameOwner, ageOwner, bioOwner) VALUES ('', '', '')
Cannot add or update a child row: a foreign key constraint fails (`DB`.`owner`, CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`memberID`) REFERENCES `members` (`memberID`) ON DELETE CASCADE ON UPDATE CASCADE)

I've already tried truncating my parent table but it still comes up with this error. I'm a beginner at database design so please explain like I'm 5.

OK - like you're five: you're trying to insert a row in a child row that has no parent it can refer to. — , May 15 '15 at 04:03
@HoboSapiens There is something very wrong with "OK - like you're five..." — Ajoy, May 15 '15 at 04:29
@Ajoy - I am merely obliging the OP in his own request. Read his last sentence. — , May 15 '15 at 04:38
possible duplicate of [Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/5005388/cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails) — , May 15 '15 at 04:40
@HoboSapiens Ah! My mistake. I only read the code. Didn't see the last part. Apologies! — Ajoy, May 15 '15 at 04:43

paxdiablo · Answer 1 · 2015-05-15T05:50:34.307

CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`memberID`) REFERENCES `members` (`memberID`)

This constraint on the owner table means that every row in that table needs to have a valid memberID. It is only considered valid if it exists in the memberID column of the members table.

Your insert statement:

INSERT INTO owner (nameOwner, ageOwner, bioOwner) VALUES ('', '', '')

does not provide a memberID to be inserted so whatever is being used¹ is obviously not in the members table. Hence the constraint violation.

Easiest fix is to provide a valid memberID in your insert statement, one that already exists in members.

By way of an example, this would work, assuming you don't yet have a 42 in members:

insert into members (memberID, username, password) values (
    42,
    'paxdiablo',
    'never_you_mind');
insert into owner (nameOwner, ageOwner, bioOwner, memberID) values (
    'Pax Diablo',
    'too damn old',
    'no idea what this should be',
    42);

If you don't know what the member ID should be, you can use something like:

insert into owner (nameOwner, ageOwner, bioOwner, memberID) 
    select 'Pax Diablo',
           'too damn old',
           'no idea what this should be',
           memberID
      from members where username = 'paxdiablo';

though you'd probably want members.memberID to be unique.

¹ What actually happens here depends on the version of MySQL you're using and whether it's running in strict SQL mode or not. I think, from memory, it will either fail the insert or use zero as the default.

So then I have to edit my insert statement to INSERT INTO owner (nameOwner, ageOwner, bioOwner,memberID) VALUES ('xx', 'xx', 'xx','') ? Would I keep its VALUE empty like presented above? Thanks for your help btw, I really appreciate it. — laimingl, May 15 '15 at 04:39
@laimingl, that'll only work if you have a member id of `''` in the `members` table, and it will link all inserts to that row. That's unlikely to be what you want. Instead, you need to find the correct record in `members` and insert its `memberID` into `owner` with the other data. — paxdiablo, May 15 '15 at 05:40

MySQL Foreign Key: Cannot add or update a child row: a foreign key constraint fails

1 Answers1