如何使用单个外键引用多个表

问题描述

你好，
我有多个表需要使用单个外键进行处理，基于偶数...或x mod y类型
例如，如果table_referenced具有x，则
如果x mod 3 = 0
然后从table1加载记录
如果x mod 3 = 1
然后从table2加载记录
如果x mod 3 = 2
然后从table3加载记录


但是像这样
创建表table_referenced
(
ref_field int引用table1(t1id)，table2(t2id)，table3(t3id)
)

我知道没有这样的命令，但是我想要做相同的事情...

hello,
i have more than one table that need to be addressed in single foreign key, base on even odds... or x mod y type
for example if table_referenced has x then
if x mod 3 = 0
then load records from table1
if x mod 3 = 1
then load records from table2
if x mod 3 = 2
then load records from table3


but like in this way
create table table_referenced
(
ref_field int references table1(t1id),table2(t2id),table3(t3id)
)

i know there is no such command but i want something that do the same thing...

推荐答案

select * from table1 where id in (select ref_field from table_referenced where ref_field % 3 = 0)
Union
select * from table2 where id in (select  ref_field from table_referenced where ref_field % 3 = 1)
Union
select * from table3 where id in (select ref_field from table_referenced where ref_field % 3 = 2)

case when [x]%(3)=(0) then [x] end

整个表看起来像这样:

The whole table looks like this :

CREATE TABLE [test].[main](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [int] NOT NULL,
	[value_mirror1]  AS (case when [value]%(3)=(0) then [value]  end) PERSISTED,
	[value_mirror2]  AS (case when [value]%(3)=(1) then [value]  end) PERSISTED,
	[value_mirror3]  AS (case when [value]%(3)=(2) then [value]  end) PERSISTED
) ON [PRIMARY]

之后，您可以定义value_mirror1和table1 id之间的关系，依此类推...

这样可以使您的一致性达到可接受的程度.

希望这会有所帮助.

如果在编辑表时收到ARITHABORT错误:右键单击SSMS中的数据库，然后在OPTIONS部分中选择属性，将 Arithmatic Abort Enabled 设置为True.

after this you can define a relation between value_mirror1 and table1 id and so on ...

This gives you a consistency to an acceptable extent.

Hope this helps.

if you get ARITHABORT error when editing your table : right click on your database in SSMS select properties in OPTIONS section set Arithmatic Abort Enabled to True.

这篇关于如何使用单个外键引用多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！