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

Question

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 1

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.