Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Foreign key problem
January 10, 2006, 1:11 pm
rate this thread
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`
CONSTRAINT `child_ibfk_2` FOREIGN KEY (`mother_id`) REFERENCES `birds`
) 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 ?
Re: Foreign key problem
On second thought, I can't find any docs that show how to use a trigger to
raise an error when you violate a constraint. The trigger can alter the
values in the new row inserted, but it can't "error out" to abort the
So I retract my recommendation. Triggers in MySQL seem to be less useful
than I thought. I was thinking of InterBase/Firebird, in which you can
raise an exception in a trigger.
You'll have to enforce such data rules in your application code, prior to
insert/update of the data.
- » Pls recommend one C++ wrapper for unixODBC/MyODBC api
- — Next thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum