how to reference more than one table using a single foreign key

Question

3.00/5 (2 votes)

See more:

SQL-Server-2005

, +

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...

Posted 27-Oct-11 7:56am

psychic6000

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Accepted Answer · 2011-10-27T18:30:00

Solution 1

Your design is very bad, but you can use something like this:

SQL

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)

Posted 27-Oct-11 18:30pm

Mehdi Gholam

Comments

psychic6000 29-Oct-11 5:31am

thank you, this worked, but as you said a bad design, so i changed the design,

Amir Mahfoozi · Accepted Answer · 2011-10-28T21:49:00

Hi psychic6000,

As you mentioned there is no such command but there is a trick to implement such a relation.

You can add tree computed fields in addition to X and set their persisted attribute to ON , for making it possible to define them as a foreign key.
Their formula is something like this :

SQL

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

The whole table looks like this :

SQL

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]

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.

EDIT: 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.