CODEWITHSUNDEEP
mysqlsqlmariadb
Lehks
Lehks

Reputation: 3196

Preventing two foreign keys to be NULL at the same time

In MySQL / MariaDB, how can I prevent two foreign keys from being NULL at the same time? This seems quite simple at first, I used a CREATE statement constraint like this:

CREATE TABLE `Table` (
    `foo_id` INT UNSIGNED UNIQUE DEFAULT NULL,
    `bar_id` INT UNSIGNED UNIQUE DEFAULT NULL,
    CONSTRAINT `CHECK_keys_present` CHECK (
        ((`foo_id` IS NOT NULL) OR (`bar_id` IS NOT NULL)) != FALSE
    ),
    FOREIGN KEY (`foo_id`) REFERENCES `Foo` (`foo_id`) ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY (`bar_id`) REFERENCES `Bar` (`bar_id`) ON UPDATE CASCADE ON DELETE SET NULL
);

This solution works, as long as I manually update the values in the columns foo_id and bar_id. In this case, the columns do not become NULL at the same time. However, when I delete rows from the Table Foo or Bar (which then sets the values of the foreign keys to NULL because of the ON UPDATE clauses) the constraint CHECK_keys_present does not trigger.

Also, I've use tried using BEFORE UPDATE triggers, but these do not seem to work as either.

I am using MariaDB version 10.4.8.

Also I've used the weird ((`foo_id` IS NOT NULL) OR (`bar_id` IS NOT NULL)) != FALSE construct, because just (`foo_id` IS NOT NULL) OR (`bar_id` IS NOT NULL) did not work.

Upvotes: 3

Views: 153

Answers (2)

Rick James
Rick James

Reputation: 142528

FOREIGN KEYs, TRIGGERs, CHECK, etc, have only basic capabilities. More complex things need to be done in application code or in a Stored Procedure.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271031

This is not fully an answer, because I haven't found a reference specific to check constraints. However, MariaDB does not enforce triggers on foreign key cascades:

The following restrictions apply to triggers.

. . . - Triggers are not activated by foreign key actions.

I would speculate that check constraints fall into the same category.

This would imply that you cannot do what you want with a cascading foreign key constraint.

Upvotes: 2

Related Questions