473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
2 1195
<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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Jeremiah Jacks | last post by:
I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using = the pre-compiled binaries. I have a database with INNODB tables. When I insert a row into one of the child tables, I get...
1
by: Andrew DeFaria | last post by:
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB...
2
by: Gunnar Vøyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
10
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed...
1
by: Jason Madison | last post by:
We sometimes get very large databases that we want to cut down to use for testing. The information is all related to a central accounts table. The way I thought of doing this is to grab all...
0
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A...
10
by: Shawn Chisholm | last post by:
Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a...
2
by: Ian Davies | last post by:
I have created a database with about 17 tables. I have been creating foreign keys some of which have worked but when creating others I get the message below ************************* 1005...
9
by: sonal | last post by:
Hi all, I hv started with python just recently... and have been assigned to make an utility which would be used for data validations... In short we take up various comma separated data files for...
2
by: jarea | last post by:
I have read quite a bit about this error but I have yet to find the solution to my problem. I am trying to execute the following mysql statement: alter table line_items add...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.