多对多关系表的唯一约束

本文介绍了多对多关系表的唯一约束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

是否有任何方法可以添加约束以确保一个列中的X条目只能允许另一列中的Y条目?

说我有两个表，精简到最少的列，tbl_1有一个pk. tbl_2有2列-一个pk和一个文本字符串.

Say I have two tables, stripped down to minimal columns, tbl_1 has a pk. tbl_2 has 2 columns - a pk and a text string.

这些表由第三关系表连接，因为它们之间存在很多关系，并且使用tbl1和tbl2中的pk.

These tables are joined by a 3rd relationship table because they're many to many and it uses pk from tbl1 and tbl2.

t1_pk t2_pk | t2_str t1fk | t2fk x 1 AAA x 1 y 2 BBB x 2 z 3 AAA y 3 4 BBB y 4 z 1 z 2

上面的所有条目都是允许的，但是现在我试图弄清楚如何限制关系表，以便附加到t2_pk的字符串只能绑定到t1_pk ONCE.例如.在第三张表中:

All entries above are allowed, but now I'm trying to figure out how I can constrict the relationship table so that the string attached to t2_pk can only tie in to the t1_pk ONCE. E.g. in the 3rd table:

t1fk | t2fk x 3

将不被允许，因为x-1存在并且1和3都附加了字符串AAA.我可以想到的一种无需再创建3个表并绕圈的方式是将字符串移动到关系表并添加约束，因此，如果表中已经存在t2fk数字，则如果再加上该数字，则仅允许该数字再次出现相同的字符串.

would not be allowed because x-1 exists and both 1 and 3 have the string AAA attached. One way I can think without making 3 more tables and going round in circles, is to move the string to the relationship table and add a constraint so if the t2fk number exists in the table already it'll only allow the number again if accompanied by the same string.

是否可以为此设置一个后台进程，比如添加一个唯一约束，还是只需要由存储过程强加?

推荐答案

您可以在包含关系的第三个表中添加t2_str列，或者为此目的创建一个新表.这里有一个示例，说明如何实现新表tab_constr.

Either you add a t2_str column to your third table that contains the relations or you create a new table for this purpose. Here an example how to implement the new table tab_constr.

drop table if exists tab_constr; drop table if exists tab_rel; drop table if exists tab_1; drop table if exists tab_2; CREATE TABLE tab_1 ( t1_pk varchar(5), PRIMARY KEY (t1_pk) ); CREATE TABLE tab_2 ( t2_pk INT, t2_str varchar(10) NOT NULL, PRIMARY KEY (t2_pk), INDEX(t2_pk, t2_str) ); CREATE TABLE tab_rel ( t1_pk varchar(5), t2_pk INT, PRIMARY KEY (t1_pk,t2_pk), INDEX (t2_pk), FOREIGN KEY (t1_pk) REFERENCES tab_1(t1_pk), FOREIGN KEY (t2_pk) REFERENCES tab_2(t2_pk) ); CREATE TABLE tab_constr ( t1_pk varchar(5), t2_str varchar(10), t2_pk int, PRIMARY KEY pair_already_exists(t1_pk,t2_str), INDEX(t1_pk, t2_pk), INDEX(t2_pk, t2_str), FOREIGN KEY (t1_pk, t2_pk) REFERENCES tab_rel(t1_pk, t2_pk) ON DELETE CASCADE, FOREIGN KEY (t2_pk, t2_str) REFERENCES tab_2(t2_pk, t2_str) ON UPDATE CASCADE ); CREATE TRIGGER tr_ins_rel AFTER INSERT ON tab_rel FOR EACH ROW BEGIN INSERT INTO tab_constr ( t1_pk, t2_str, t2_pk) select new.t1_pk, t2_str, new.t2_pk from tab_2 where t2_pk=new.t2_pk ; END; INSERT INTO tab_1 (t1_pk) VALUES ('x'); INSERT INTO tab_1 (t1_pk) VALUES ('y'); INSERT INTO tab_1 (t1_pk) VALUES ('z'); INSERT INTO tab_2 (t2_pk,t2_str) VALUES (1, 'AAA'); INSERT INTO tab_2 (t2_pk,t2_str) VALUES (2, 'BBB'); INSERT INTO tab_2 (t2_pk,t2_str) VALUES (3, 'AAA'); INSERT INTO tab_2 (t2_pk,t2_str) VALUES (4, 'BBB'); INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('x', 1); INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('x', 2); INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('y', 3); INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('y', 4); INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('z', 1); INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('z', 2); commit;

以下语句将引发错误:

INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('x', 3);

这也会引发错误

UPDATE tab_2 set t2_str='BBB' where t2_pk=1;

但这会起作用

DELETE FROM tab_rel where t1_pk='x' and t2_pk=1; INSERT INTO tab_rel (t1_pk,t2_pk) VALUES ('x', 3);

这将起作用

UPDATE tab_2 set t2_str='XXX' where t2_pk=1;

在这里，您可以尝试一下.

当然，此附加表违反了正常形式，并为数据库增加了冗余.但这没有问题，因为此表tab_constr是一个辅助结构，如索引，并且它将由数据库自动维护.因此不会发生插入/更新/删除异常.

Of course this additional table violates normal form and adds redundancy to your database. But that is no problem because this table tab_constr is an auxiliary structure like an index and it will be automatically maintained by the database. So no insert/update/delete anomalies will occur.