Foreign key problem

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 ?

Re: Foreign key problem

wrote in message
news:1136898671.673964.59520@f14g2000cwb.googlegroups.com...
> 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.

The only way to do this using the schema is to use a trigger.
To do that you need to use MySQL 5.0.

Regards,
Bill K.

Re: Foreign key problem

"Bill Karwin" wrote in message
news:dq3tba0msq@enews4.newsguy.com...
> wrote in message
> news:1136898671.673964.59520@f14g2000cwb.googlegroups.com...
>> 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.
>
> The only way to do this using the schema is to use a trigger.
> To do that you need to use MySQL 5.0.

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
operation.

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.

Regards,
Bill K.