How to do a negative foreign key check (constraint to foreign table) in MySQL?

Question

I want to make sure, that a column doesn't have a value that exists in another column of another table. It is kind of like a negative foreign key check, if you will.

However adding a constraint doesn't work:

CHECK (`column` NOT IN `Other Table`)

-> Syntax Error

CHECK (`column` NOT IN (SELECT `other column` FROM `Other Table`))

-> Function or expression 'select ...' cannot be used in the CHECK clause

The following is an example, in case someone has a smarter schema. Note, that I am still interested in how to do it generally even if this schema can be redesigned.

This is a redirection mapping between mailboxes and I want to make sure, that the from mailbox doesn't exist.

CREATE TABLE `Alias` ( `from` varchar(128) NOT NULL, `to` varchar(128) NOT NULL, UNIQUE KEY (`from`), FOREIGN KEY (`to`) REFERENCES `Mailbox`(`name`) ON UPDATE CASCADE );

(Yes, VARCHAR is a bad PK type; no I don't care.)

I doubt, if CHECK-constraint in any RBDMS can refer to different table — Sergey
– Sergey, Commented May 15 at 12:57
@Sergey but that's exactly what foreign key constraints do :). but you're right that there isn't likely to be CHECK support for this case in any RDMBS. — ysth
– ysth, Commented May 15 at 13:04
There is probably a better way of designing your database, can you post more of the DDL, including the parent and child tables? Perhaps a different design, such as polymorphic association or something. — Charlieface
– Charlieface, Commented May 15 at 13:04
@Sergey In SQL Server you can do it with a function in a CHECK constraint, although it's not recommended and has serious locking issues. SQL Server also has the option for an index view trick, see spaghettidba.com/2011/08/03/… — Charlieface
– Charlieface, Commented May 15 at 13:05

Bill Karwin · Accepted Answer · 2025-05-15 14:36:42Z

A CHECK constraint can reference columns of the same row, but little else.

https://dev.mysql.com/doc/refman/en/create-table-check-constraints.html says in part:

CHECK condition expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

Stored functions and loadable functions are not permitted.

Subqueries are not permitted.

This means you can't make a CHECK constraint that depends on other rows of the same table, nor any rows in a different table.

ANSI SQL has another type of constraint that could be used to solve your case. It's called an ASSERTION. It's like a CHECK constraint, but it doesn't belong to any table, and it can reference multiple tables. Basically, any time you modify any data in any table, all the assertion constraints are evaluated, and they must all be true. You can imagine that this could become a huge performance problem, so assertion constraints should be used very sparingly.

MySQL does not support assertion constraints. As far as I know, not many other brands of SQL database do either.

To solve your problem in MySQL, you can either use a trigger or implement the logic in your application code.

Example:

CREATE TRIGGER mb_must_not_exist BEFORE INSERT ON Alias FOR EACH ROW BEGIN IF EXISTS(SELECT * FROM Mailbox WHERE name = NEW.from) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot create alias because a mailbox by that name already exists.'; END IF; END

Also consider that you'd need a similar trigger on your Mailbox table, to block creation of a mailbox if an alias exists with the same name.

And also a trigger before UPDATE for both tables too, to prevent someone from changing a mailbox name or alias name to conflict with an existing name.

Solving this with either a trigger or application code is susceptible to race conditions. That is, the trigger might not detect an existing mailbox, because the transaction the trigger is running in can't see the mailbox, because the mailbox is being inserted by a transaction that isn't committed yet. So the trigger allows the alias to be created, and then a millisecond later, the mailbox of the same name is committed.

The same problem exists if you use application code to SELECT to check existence of either the mailbox or the alias. Immediately after you SELECT, the name could be committed.

One way to avoid the race condition problem is with a global lock during both mailbox creation and alias creation.

Collectives™ on Stack Overflow

How to do a negative foreign key check (constraint to foreign table) in MySQL?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related