optional relationships 2

[jjjamie]

Is it possible to have optional relationships in mySQL? I have a table that stores jobs carried out by engineers and uses foreign keys to link to the different types of job that they may do. Here is the table:

CREATE TABLE job(
   job_id           INT AUTO_INCREMENT,
                    PRIMARY KEY(job_id),
   engineer_id      INT NOT NULL,
                    INDEX engineer_ind (engineer_id),
                    FOREIGN KEY (engineer_id)
                    REFERENCES engineer(engineer_id),
   reference        VARCHAR(10),
   submitdate       DATE NOT NULL,
   submittime       TIME NOT NULL,
   onsiterepair_id  INT,
                    INDEX onsiterepair_ind (onsiterepair_id),
                    FOREIGN KEY (onsiterepair_id)
                    REFERENCES onsiterepair(onsiterepair_id),
   servicereport_id INT,
                    INDEX servicereport_ind (servicereport_id),
                    FOREIGN KEY (servicereport_id) 
                    REFERENCES servicereport(servicereport_id),
   pattest_id       INT,
                    INDEX pattest_ind (pattest_id),
                    FOREIGN KEY (pattest_id)
                    REFERENCES pattest(pattest_id),
   pressuretest_id  INT,
                    INDEX pressuretest_ind (pressuretest_id),
                    FOREIGN KEY (pressuretest_id) 
                    REFERENCES pressuretest(pressuretest_id)
);

(The different types of job are onsiterepair, servicereport, pattest and pressuretest)

Quite often an engineer will only carry out 1 or 2 of the types of job types available. In that case a query such as

INSERT job VALUES(NULL, 1, "12423/2", "2002-06-02", "12:00", 3, 2, NULL, NULL);

would be ideal but mySQL gives the error "Cannot add a child row: a foreign key constraint fails". Is there any way to make the foreign key relationships optional? I would prefer to keep the keys in if at all possible. I have thought about just having a "dummy" row in each of the 4 linked tables that I could link to instead of using NULL (e.g. row 1 could be a dummy row), but this doesn't seem a very elegant solution.

Thanks in advance for any help you may be able to provide.

 

[csmith3pmg]

According to the documentation:

Starting from version 3.23.50, InnoDB does not check foreign key constraints on those foreign key or referenced key values which contain a NULL column.

If you are running into problems with NULLs being compared with your foreign key constraint then you may need to upgrade. If you are at or above 3.23.50 then check to make sure that you have a record in engineer with engineer_id = 1 and an onsiterepair record with onsiterepair_id = 3 and a servicereport record with servicereport_id = 2.

If those three records exist then the insert should work correctly. Foreign key constraints are relatively new to mySQL so if it still isn't working after checking all of that then you may have run across a bug.

Hope that helps,
Chris

 

[jjjamie]

I have just checked my installation and I have 4.0.1-alpha. Here's the version string provided by MySql...

mysql Ver 11.19 Distrib 4.0.1-alpha, for Win95/Win98 (i32)

This version should be okay shouldn't it? I have just tried creating some test tables to test what you said, but it still didn't work. Here's what I tried:

CREATE TABLE child(
  x    INT NOT NULL, PRIMARY KEY(x)
) TYPE=INNODB;

CREATE TABLE parent(
  x    INT,
  INDEX (x),
  CONSTRAINT FOREIGN KEY (x) REFERENCES child(x)
);

mysql> INSERT child VALUES(1);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT parent VALUES(NULL);
ERROR 1216: Cannot add a child row: a foreign key constraint fails

It's very strange! Are you sure that you can provide NULL values for foreign keys?

 

[RubiconCSL]

What I like to do with all my dimension tables (i.e. lookup tables or your "child" tables) is to create a dummy row, which I always populate when the table is created with a value of -1.

Then, whenever you have no real value to enter, put -1 instead of NULL. It is also very helpful, in that you don't need to worry about outer joins, because the value (-1) will always exist there.

So, in your example, whack a -1 in pattest and pressuretest as the key and then - hey presto - it will work and your data integrity will be in tact.

It's also very useful, because as well as -1 (meaning effectively 'NOT FOUND'), I whack a -2 for 'NOT APPLICABLE', because maybe pattest isn't applicable for all engineers. A subtle difference between NOT FOUND and NOT APPLICABLE I know, but still a good idea.

Finally, -1 or -2 is a definite and obviously concious value. NULL maybe there because someone forgot to enter a value.

QED.

 

[jjjamie]

I see your point. I will probably use -1 as 'NOT APPLICABLE' (being more appropriate than NULL). I still find it strange that I can't use NULL values though...

 

[jjjamie]

I have just upgraded to 4.0.4-beta-max and it now works fine Thanks for your help

 

[gshomedesign]

Hi jjjamie, I noticed that you db is for engineer work. I am starting a db for architectural work and could use all the advice I can get. Look at my forum post about my needs and maybe you could give me some insight. I just posted yesterday.