在两个表中强制使用唯一值 [英] Enforce unique values across two tables
问题描述
是否可以在MySQL中的两个表中强制实现唯一性?
Is it possible to enforce uniqueness across two tables in MySQL?
我有两个表,都描述了用户。这些表中的用户以前用于两个不同的系统,但是现在我们正在合并我们的身份验证系统,我需要确保这两个表中有唯一的用户名。 (现在把它们全部放到一个表中太多了。)
I have two tables, both describing users. The users in these tables were for two different systems previously, however now we're merging our authentication systems and I need to make sure that there are unique usernames across these two tables. (it's too much work to put them all into one table right now).
推荐答案
你不能声明一个UNIQUE约束多个表,MySQL根本不支持CHECK约束。但是您可以设计一个触发器来搜索另一个表中的匹配值。这是一个测试SQL脚本:
You can't declare a UNIQUE constraint across multiple tables, and MySQL does not support CHECK constraints at all. But you can design a trigger to search for the matching value in the other table. Here's a test SQL script:
DROP TABLE IF EXISTS foo;
CREATE TABLE FOO (username VARCHAR(10) NOT NULL);
DROP TABLE IF EXISTS bar;
CREATE TABLE BAR (username VARCHAR(10) NOT NULL);
DROP TRIGGER IF EXISTS unique_foo;
DROP TRIGGER IF EXISTS unique_bar;
DELIMITER //
CREATE TRIGGER unique_foo BEFORE INSERT ON foo
FOR EACH ROW BEGIN
DECLARE c INT;
SELECT COUNT(*) INTO c FROM bar WHERE username = NEW.username;
IF (c > 0) THEN
-- abort insert, because foo.username should be NOT NULL
SET NEW.username = NULL;
END IF;
END//
CREATE TRIGGER unique_bar BEFORE INSERT ON bar
FOR EACH ROW BEGIN
DECLARE c INT;
SELECT COUNT(*) INTO c FROM foo WHERE username = NEW.username;
IF (c > 0) THEN
-- abort insert, because bar.username should be NOT NULL
SET NEW.username = NULL;
END IF;
END//
DELIMITER ;
INSERT INTO foo VALUES ('bill'); -- OK
INSERT INTO bar VALUES ('bill'); -- Column 'username' cannot be null
对于每个表,您还需要类似的触发器ON UPDATE,但你不应该需要任何触发器来删除。
You also need similar triggers ON UPDATE for each table, but you shouldn't need any triggers ON DELETE.
这篇关于在两个表中强制使用唯一值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!