I am breeder of birds. I attempt to make one table BIRDS. It looks
like this
CREATE TABLE `birds` (`id` int(11) not NULL auto_increment,
`father_id` int(11) NULL,
`mother_id` int(11) NULL,
`sexe` varchar(1) NOT NULL default 'M',
PRIMARY KEY (`id`),
KEY `father_id` (`father_id`),
KEY `mother_id` (`mother_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`father_id`) REFERENCES `birds`
(`id`),
CONSTRAINT `child_ibfk_2` FOREIGN KEY (`mother_id`) REFERENCES `birds`
(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
The fields father_id and mother_id can be null, i do not always know
the father. But when the field father_id is filled in with an id, then
that id has to be in the same table. When it is left blank, then no
check has to be done.
A second question : It would be nice if i fill in father_id, then there
should be a check that the id is in the table (first question) but that
that id is from a bird which sexe is M.
Can someone help me ?