What If Foreign Key Can Be Null

[Luc_40]

I have a table in which there are id's referencing to another table. But the value in the main table can also be null. How do i solve this ? Here is my table description …

Table Create Table

---

---

vogels CREATE TABLE `vogels` (

`id` int(11) NOT NULL auto_increment,

`vader_id` int(11) default NULL,

`moeder_id` int(11) default NULL,

`kwekers_id` int(11) default NULL,

`geslacht_cd` int(11) default NULL,

`gekuifd_cd` int(11) default NULL,

`kleur_cd` int(11) default NULL,

`dt_in` date default NULL,

`reden_in_cd` int(11) default NULL,

`dt_uit` date default NULL,

`reden_uit_cd` int(11) default NULL,

`geboorte_jr` varchar(4) default NULL,

`ring_nr` varchar(15) default NULL,

`tekst` varchar(200) default NULL,

PRIMARY KEY (`id`),

KEY `FK_vogels_vader_id` (`vader_id`),

KEY `FK_vogels_moeder_id` (`moeder_id`),

KEY `FK_vogels` (`kwekers_id`),

KEY `FK_vogels_geslacht_cd` (`geslacht_cd`),

KEY `FK_vogels_gekuifd_cd` (`gekuifd_cd`),

KEY `FK_vogels_kleur_cd` (`kleur_cd`),

KEY `FK_vogels_reden_in_cd` (`reden_in_cd`),

KEY `FK_vogels_reden_uit_cd` (`reden_uit_cd`),

CONSTRAINT `vogels_ibfk_3` FOREIGN KEY (`kwekers_id`) REFERENCES `kwekers` (`id`),

CONSTRAINT `vogels_ibfk_4` FOREIGN KEY (`geslacht_cd`) REFERENCES `codes` (`id`),

CONSTRAINT `vogels_ibfk_5` FOREIGN KEY (`gekuifd_cd`) REFERENCES `codes` (`id`),

CONSTRAINT `vogels_ibfk_6` FOREIGN KEY (`kleur_cd`) REFERENCES `codes` (`id`),

CONSTRAINT `vogels_ibfk_7` FOREIGN KEY (`reden_in_cd`) REFERENCES `codes` (`id`),

CONSTRAINT `vogels_ibfk_8` FOREIGN KEY (`reden_uit_cd`) REFERENCES `codes` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

For instance the field reden_uit_cd can be necessary for that record, but for other records not necessary. But when it is filled in, then it has to be with a value of that other table.

Thx on advance

[peterlaursen]

I don't understand this. Could you explain somewhat more in detail, what is the exact nature of the problem. Do you get some error messages, or what kind of problem arises.

Also please observe that a 'reproducable test case' preferably reduces the complexity of the problem. Would it not be sufficent for the 'test case' to have the two colums

Code:

`vader_id` int(11) default NULL
`moeder_id` int(11) default NULL

having FK's ??

You are asking a MySQL question not a SQLyog question, so please observe that you will have to prepare things to be easy to get started with for people interesting in supporting this.

[Luc_40]

I want to do the following. I am a breeder of birds. I want a table TEST in which i have an id, and a field that points to the father of that bird. So it is a foreign key to the same table. Sometimes i don't know the father and then i like to leave that field empty.

I can not solve this. Please give me an example.

/*Column Information For – glosters.test*/

---

Field Type Collation Null Key Default Extra Privileges Comment

---

---

---

---

---

---

---

---

---

ID int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update,references

FATHER_ID int(11) (NULL) YES MUL (NULL) select,insert,update,references

TEXT varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references

/*Index Information For – glosters.test*/

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

test 0 PRIMARY 1 ID A 0 (NULL) (NULL) BTREE

test 1 FATHER_ID 1 FATHER_ID A 0 (NULL) (NULL) YES BTREE

/*DDL Information For – glosters.test*/

---

Table Create Table

---

---

test CREATE TABLE `test` (

`ID` int(11) NOT NULL auto_increment,

`FATHER_ID` int(11) default NULL,

`TEXT` varchar(50) default NULL,

PRIMARY KEY (`ID`),

KEY `FATHER_ID` (`FATHER_ID`),

CONSTRAINT `test_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `test` (`FATHER_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

[peterlaursen]

I think you should use 0 (zero) or maybe -1 or -99 for 'father unknown' and not NULL. NULL means 'no data availabale' or 'not decided yet'. And 'father unknown' is 'SOME data'

And this

Quote:

So it is a foreign key to the same table

won't work! A foreign key is FOREIGN – that means from outside the table.

In principle you'll need only one table for this. Could be like

Code:

CREATE TABLE `birds` (                      
         `id` bigint(20) NOT NULL auto_increment,  
         `father_id` bigint(20) default NULL,      
         `name` varchar(50) default NULL,          
         `comment` varchar(255) default NULL,      
         PRIMARY KEY  (`id`),                      
         KEY `father_id` (`father_id`)            
       ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

You could also let farther_id be defined like

Code:

`father_id` bigint(20) default '-1'

You may even have more code like:

-1 meaning 'unknown father own breed'

-2 meaning 'unknown father from breeeder DONALD DUCK

-3 meaning 'unknown father from breeeder MICKEY MOUSE

-99 meaning 'unknown father from unknown breeeder

… though a breeder table here probably would be better! 

But you can also create a seperate table for all birds being fathers. They will then occur in both tables. The father table will be very simple – having only an ID and a NAME field. Much depends on how complicated queries you'll need to do.

Also see attached!

[peterlaursen]

@Ritesh:

BTW: what is a 'defination' ? 😛

[Luc_40]

Strange, in the help, under version history, march 2003, it says 'You can make foreign key relationships within same table'. And that is exactly what i need, but i need also that this field can be null.

[peterlaursen]

Quote:

Strange, in the help, under version history, march 2003, it says …

well yes it is possible to do so. But if I understand your example correctly (it is a little bit different to have your table schema in my head because of the language. It would be easier if I understood the words) you are indexing a column to itself. And I don't think that makes much sense.

I don't understand why you can't use 0 (zero) or -1 instead of NULL, if using NULL is a problem? You probabably know the difference of 0 and NULL? I apologize if it is trivial to you.

But I also don't think I have understood your 'project' very well. In broad terms, yes, but the correlation between the data, the table design and the queries that you need to do, no. Hopefully someone will understand better than I.

[Luc_40]

A last attempt to become an answer. Next situation. I have one table 'persons' in which i put all the persons. Each person has a father. Sometimes the father is unknown. In that case i want to leave the field father_id blank. When the father is known, then the father_id has to be filled in, and the id has to reference a record in the same table persons where the sexe_cd is like 'M' (from male). When the father_id is not filled in, then it is also okay. When it is filled in with a wrong id then there has to be an error.

Can someone tell me how to do ?

[Ritesh]

I can only comment that this can be done in MySQL and its pretty trivial and common. I think CalEvans will be able to help you out on this issue. His SQL is better then mine 🙂

[Luc_40]

CalEvans, please help me as soon as you can …

[CalEvans]

Luc_40 wrote on Jan 9 2006, 06:37 PM:

CalEvans, please help me as soon as you can …

[post=”8399″]<{POST_SNAPBACK}>[/post]

My First question is why define the constraint if it's optional. If the value can be null the the contrstrint is optional. It's ok to have these in oyur modle but you may not want to bother defining it in your physical schema.

If you feel you must have it (i.e. for cascading delete purposes) then your update clause needs to be NO ACTION

i.e.

Alter table bird add Foreign Key (father_id) references bird (vogel_id) on delete cascade on update no action;

I've not tried this but I believe it's what you are looking for.

Let me knof if this doesn't work and I'll try again.

=C=

[Luc_40]

birds CREATE TABLE `birds` (

`id` int(11) NOT NULL auto_increment,

`father_id` int(11) default NULL,

`mother_id` int(11) default NULL,

`text` varchar(10) default '',

PRIMARY KEY (`id`),

KEY `father_id` (`father_id`),

KEY `mother_id` (`mother_id`),

CONSTRAINT `birds_ibfk_1` FOREIGN KEY (`father_id`) REFERENCES `birds` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,

CONSTRAINT `birds_ibfk_2` FOREIGN KEY (`mother_id`) REFERENCES `birds` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Sorry, but it isn't working. Try the above create and then try to insert a record where you only will fill in the field text.

[CalEvans]

Luc_40 wrote on Jan 11 2006, 02:21 PM:

Sorry, but it isn't working.  Try the above create and then try to insert a record where you only will fill in the field text.

[post=”8408″]<{POST_SNAPBACK}>[/post]

Hmmm…ok, the only thing else I can think of is a seperate join table called father. Then create 2 optional joins into it from bird where the first join is bird_id and the second is father_id.

In most cases when I have something ike what you are trying to do I insert a record into bird with the ID 0 and make it 'Unknown'. This way father_id of 0 is valid.

=C=

[Luc_40]

Okay, thanks for the research. I am not so happy with the solution but i can live with it. In Oracle (i am oracle programmer / dba) i have done it already and it works fine.

Thanks to all for the answers. This topic may be closed.

[peterlaursen]

The restriction is a restriction with the InnoDB storage engine a such. Not the MySQL server and not SQLyog.

I know nothing about storage/indexing systems used by Oracle DBMS but actually Oracle just purchased InnoDB!!!

MySQL plan to allow for Foreign Keys with MyISAM. I believe it is in the roadmap for MySQL 5.1. So maybe in a year or so…

[CalEvans]

peterlaursen wrote on Jan 11 2006, 05:46 PM:

Oracle just purchased InnoDB!!! 

MySQL plan to allow for Foreign Keys with MyISAM. I believe it is in the roadmap for MySQL 5.1. So maybe in a year or so…

[post=”8415″]<{POST_SNAPBACK}>[/post]

I think the 2 are related. 🙂

=C=

[peterlaursen]

Actually it is about ½ year ago that I read about the plans with FK's and MyISAM at the MySQL Forums. Oracle's purchase of InnoDB is only about 2 months. However the management at MySQL probably have been informed that something should happen. Most likely they have been asked too what they would pay!

[Author]

Posts