467,222 Members | 1,430 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,222 developers. It's quick & easy.

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 ?

Jan 10 '06 #1
  • viewed: 1073
Share:
2 Replies
<fi**********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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.
Jan 11 '06 #2

"Bill Karwin" <bi**@karwin.com> wrote in message
news:dq********@enews4.newsguy.com...
<fi**********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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.
Jan 12 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
2 posts views Thread by Gunnar Vyenli | last post: by
10 posts views Thread by Bodza Bodza | last post: by
1 post views Thread by Jason Madison | last post: by
10 posts views Thread by Shawn Chisholm | last post: by
2 posts views Thread by Ian Davies | last post: by
reply views Thread by Adict | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.