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

i've created several tables, 1 being parent table , 3 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 table i've gotten error:

insert owner (nameowner, ageowner, bioowner) values ('', '', '') cannot add or update child row: foreign key constraint fails (`db`.`owner`, constraint `owner_ibfk_1` foreign key (`memberid`) references `members` (`memberid`) on delete cascade on update cascade) 

i've tried truncating parent table still comes error. i'm beginner @ database design please explain i'm 5.

constraint `owner_ibfk_1` foreign key (`memberid`) references `members` (`memberid`) 

this constraint on owner table means every row in table needs have valid memberid. considered valid if exists in memberid column of members table.

your insert statement:

insert owner (nameowner, ageowner, bioowner) values ('', '', '') 

does not provide memberid inserted whatever being used¹ not in members table. hence constraint violation.

easiest fix provide valid memberid in insert statement, 1 exists in members.

by way of example, work, assuming don't yet have 42 in members:

insert members (memberid, username, password) values (     42,     'paxdiablo',     'never_you_mind'); insert owner (nameowner, ageowner, bioowner, memberid) values (     'pax diablo',     'too damn old',     'no idea should be',     42); 

if don't know member id should be, can use like:

insert owner (nameowner, ageowner, bioowner, memberid)      select 'pax diablo',            'too damn old',            'no idea should be',            memberid       members username = 'paxdiablo'; 

though you'd want members.memberid unique.

---

¹ happens here depends on version of mysql you're using , whether it's running in strict sql mode or not. think, memory, either fail insert or use 0 default.