CODEWITHSUNDEEP
mysqlsqldatabase-designinnodbmysql-5.5
Blackbam
Blackbam

Reputation: 19376

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.

Upvotes: 11

Views: 3589

Answers (4)

David Vielhuber
David Vielhuber

Reputation: 3569

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

Upvotes: 4

JohnLBevan
JohnLBevan

Reputation: 24430

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://stackoverflow.com/a/37536428/361842

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.

Upvotes: 1

nvogel
nvogel

Reputation: 25526

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.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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.

Upvotes: 6

Related Questions