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.

user_2068362 · Answer

setup

consider 3 tables

humans
cats
dogs

and another table

examinations

an examination can possibly be of a human xor of a cat xor of a dog.

the following three variants are in my opinion all possible:

variant 1 (leave it inside one table)

examinations

id
...
human_id
cat_id
dog_id
check constraints/triggers that only one of these three values can be null

variant 2 (create relation tables)

humans_examinations

id
human_id
examination_id

cats_examinations

id
cat_id
examination_id

dogs_examinations

id
dog_id
examination_id

variant 3 (create a supertype)

patients

id
... (possible other common fields of human/cat/dog)

human

id
...
patient_id

cat

id
...
patient_id

dog

id
...
patient_id