一个只允许两个表中的一个引用基表的约束 [英] A constraint that only allows one of two tables to reference a base table

问题描述

我有3个表。基表，称为表A，两个引用表A的表，称为表X和表Y.X和Y都有引用表A的外键约束.X和Y的外键也是自己的主键。

I have 3 tables. A base table, call it Table A, and two tables that reference Table A, Call them Table X and Table Y. Both X and Y have a foreign key contraint that references Table A. The Foreign Key of X and Y is also their own Primary Key.

我想知道是否可以添加一个约束，只允许其中一个表包含引用表A的recrod。如果X有一个记录引用A，那么Y不能有一个，如果Y有一个引用A的记录，那么X不能有一个。

I'd like to know if it is possible to add a constraint that will only allow one of these tables to contain a recrod that references Table A. So if X has a record that references A then Y can't have one and if Y has a record that references A then X can't have one.

？

感谢，

推荐答案

使用UDF的CHECK约束Oded的回答）不能扩展和具有差的并发。查看这些：

CHECK constraints with UDFs (which is Oded's answer) don't scale well and have poor concurrency. See these:

• 包含在CHECK约束中的标量UDF非常慢， for多线更新


• Tony Rogerson

• Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates
• Tony Rogerson

所以：

• 创建一个新表，例如TableA2XY


• 这具有TableA的PK和具有CHECK的char（1）列以允许ony X或Y.以及A的PK的唯一约束。


• tableX和tableY都有新的char（1）列，并且只有X或Y。


• tableX和tableY在两列上都有FK到TableA2XY

• create a new table, say TableA2XY
• this has the PK of TableA and a char(1) column with a CHECK to allow ony X or Y. And a unique constraint on the PK of A too.
• tableX and tableY have new char(1) column with a check to allow only X or Y respectively
• tableX and tableY have their FK to TableA2XY on both columns

这是超级键或子类型方法

This is the superkey or subtype approach

• all DRI


• 无触发器


• 没有udfs与表访问在CHECK约束。

这篇关于一个只允许两个表中的一个引用基表的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！