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.

Bans

What's your reason for wanting to avoid a trigger? If just for curiosity no worries, if it's because you've been told "triggers are bad", that's slightly incorrect. Sometimes triggers are bad; sometimes they're acceptable, depending on the scenario. This is one of the acceptable scenarios. – Transilluminate

MySQL's FKs are lame. Even with a less lame FK implementation, the cannot solve all problems. – Wicketkeeper

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.

Trapan

Thanks. Start hating MySQL but all those cheap webhosts offering shared hosting do not support anything else. Postgres and MariaDB both support it. – Bans

False economy. MySQL will probably cost you more in the long run. The fact that you would have to create a trigger for this is just ridiculous. – Commentary

Hi, i think this is not correct from normalization point of view. you have so many null foreign keys (when the database gradually grew). What if we have 4 or 5 mutually exclusive foreign keys in one table? – Conquian

Answer 2

Mutually exclusive references like this are possibly an indication of a "supertype" table missing from your design. Consider creating a new supertype table that represents all "work" - both projects and special work. The Project and Special Work tables would reference the supertype table, one-to-one, which would contain the union of all the project/work identifiers. The Time table then just needs one non-nullable foreign key referencing the supertype.

Commentary

Good idea. In my case it would complicate things as special_work has 90% different columns as well as a nullable reference to project. – Bans

I think it would simplify things. Leave the 90% of "different" attributes in the special_work table. Move the 10% of shared attributes into the new supertype table. The principle at work here is orthogonal design. You should try to avoid having the same attribute in more than one table. – Commentary

Along these lines, if special work adds to project and project has nothing extra, just create a foreign key on project pointing to special work, then remove all fields from special work which are already in project. That way, if project.specialWorkId is null, it's a project; if it has a value, that gives you the extra fields for special work. – Transilluminate

Answer 3

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

examinations

• id
• ...
• patient_id

Kidron

Answer 4

Per @GordonLinoff's answer; in MySql a trigger is the best way to achieve this constraint. Do you have a specific reason for not wanting to use a trigger?

Since you've explicitly asked for options other than triggers, here are some alternate options:

Stored Procedure

Use a stored procedure to do inserts into this table / include the validation logic here.

This ensures that anything inserting / updating via the stored procedure is protected; but has the issue that this validation may be bypassed by someone directly inserting data into the table.

That issue can be alleviated by using security to ensure that only the stored procedure has rights to insert into this table / update those fields. See this answer for more detail on setting up this security: https://mcmap.net/q/1163330/-how-to-allow-insert-through-stored-procedure-only

Client Code

Similar to the stored procedure option; if you own all code that will be performing these inserts/updates, you can add the validation outside of the database. Again, your validation can be bypassed (i.e. by going straight to the database), but that's only a concern if users have access to your database. If your application is the only way to manipulate data in the DB this is sufficient. The stored procedure option's preferable, but if there's some reason it's not viable, this is your next best option.

Asynchronous Integrity Checks

The last option would be to have some job periodically running data integrity checks and reporting on issues. This won't prevent the data going wrong, but will help you become aware of it quickly so you can investigate & resolve it. Generally I'd avoid this since preventing bad data is far better than cleaning it up later; but in some use cases this is the only option.

Amend Data Model

@SqlVogel's answer (suggesting a change of model) is also excellent; i.e. what's the difference/relationship between a project and a special case? Could they be modeled as the same thing; only with additional properties being available for one and/or the other.

Transilluminate