Blackbam Blackbam - 1 year ago 53
SQL Question

Two foreign keys, one of them not NULL: How to solve this in SQL?

I have got a table

time
. A time entry (1:n relationship) either belongs to a
project
entry or to a
special_work
entry. Either the project id or the special_work id must be set, neither both (exclusive or).

CREATE TABLE `time` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`project` int(20) NOT NULL,
`special_work` int(20) NOT NULL,
`date` date NOT NULL,
`hours` float NOT NULL,
`time_from` time DEFAULT NULL,
`time_to` time DEFAULT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`project`) REFERENCES `project`(`id`)
FOREIGN KEY (`special_work`) REFERENCES `special_work`(`id`)
) DEFAULT CHARSET=utf8;


How can I write this in SQL? Any way except for a trigger?

If you are sure this is bad database design - is there a better way to model this? However I do not want to have two different time tables.

My database ist Mysql 5.5 InnoDB.

Answer Source

Your data model is fine. In most databases, you would also add a check constraint:

alter table `time` add constraint chk_time_project_special_work
    check (project is not null xor special_work is null);

However, MySQL does not support check constraints. You can implement the logic using a trigger, if you really like.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download