SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


foreign key references multiple tables


foreign key references multiple tables

Author
Message
vignesh.ms
vignesh.ms
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 516
Hi there,

Is there any possibility to create a foreign key references more than one tables.

say for example,

Create table sample1(id int primary key)
Create table sample2(id int primary key)

Create table sample3(
id int primary key,
CONSTRAINT fk1 FOREIGN KEY REFERENCES sample1 (ID),CONSTRAINT fk1 FOREIGN KEY REFERENCES sample2 (ID))




this shows no error while creating, but in the data insertion it shows error..


please help me out.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86600 Visits: 45246
Yup, perfectly possible. It's not one foreign key though, it's two foreign keys, one referencing each of the tables


CREATE TABLE sample1(id INT PRIMARY KEY)
CREATE TABLE sample2(id INT PRIMARY KEY)

CREATE TABLE sample3(
id INT PRIMARY KEY,
CONSTRAINT fk1 FOREIGN KEY (ID) REFERENCES sample1 (ID),
CONSTRAINT fk2 FOREIGN KEY (ID) REFERENCES sample2 (ID))
GO

INSERT INTO sample1 (ID) VALUES (1),(2),(3)
INSERT INTO sample2 (ID) VALUES (1),(2),(4)


INSERT INTO sample3 (ID) VALUES (1) -- succeeds
INSERT INTO sample3 (ID) VALUES (2) -- succeeds
INSERT INTO sample3 (ID) VALUES (3) -- fails because there's no matching value in sample2



Bit of an odd thing to do, but does work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


vignesh.ms
vignesh.ms
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 516
GilaMonster (2/12/2014)
Yup, perfectly possible. It's not one foreign key though, it's two foreign keys, one referencing each of the tables


CREATE TABLE sample1(id INT PRIMARY KEY)
CREATE TABLE sample2(id INT PRIMARY KEY)

CREATE TABLE sample3(
id INT PRIMARY KEY,
CONSTRAINT fk1 FOREIGN KEY (ID) REFERENCES sample1 (ID),
CONSTRAINT fk2 FOREIGN KEY (ID) REFERENCES sample2 (ID))
GO

INSERT INTO sample1 (ID) VALUES (1),(2),(3)
INSERT INTO sample2 (ID) VALUES (1),(2),(4)


INSERT INTO sample3 (ID) VALUES (1) -- succeeds
INSERT INTO sample3 (ID) VALUES (2) -- succeeds
INSERT INTO sample3 (ID) VALUES (3) -- fails because there's no matching value in sample2



Bit of an odd thing to do, but does work.



by your example,

could sample3 contain both table values ? is there any ways ?
which means I want to keep 1,2,3,4
vignesh.ms
vignesh.ms
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 516
I have done the above by using the following, but it costs much.


create table sample(id int primary key)
create table sample2(id int primary key)


create view sampleall
As
select id from sample
union
select id from sample2


Create FUNCTION dbo.CheckFunction (@ID INT)
RETURNS BIT AS
BEGIN

IF EXISTS (SELECT 1 FROM sampleall WHERE id = @ID)
BEGIN
RETURN 1;
END

RETURN 0;

END

CREATE TABLE sample3 (ID INT NOT NULL CONSTRAINT CHK_ID CHECK (dbo.CheckFunction(ID) = 1))



hunchback
hunchback
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 639
The UNION approach is completely different from the other suggestions based on your initial requirement.

The previous suggestions are constraining that a row in the referencing table should exist in the referenced ones, in both concurrently. The UNION approach will guarantee that it exists in at least one of the tables and not in both.

Also, using the function will not guarantee that if you delete a row in any of the referenced tables then an existing row in the referencing table could become an orphan.



vignesh.ms
vignesh.ms
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 516
hunchback (2/12/2014)
The UNION approach is completely different from the other suggestions based on your initial requirement.

The previous suggestions are constraining that a row in the referencing table should exist in the referenced ones, in both concurrently. The UNION approach will guarantee that it exists in at least one of the tables and not in both.

Also, using the function will not guarantee that if you delete a row in any of the referenced tables then an existing row in the referencing table could become an orphan.


I completely accept it... but it just a try to accomplish the target.

Is there any other way which completely works like foreign key ?
hunchback
hunchback
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 639
Previous suggestions using two foreign key constraints will do it even though it is a not common design.



Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4964 Visits: 5478

...
Is there any other way which completely works like foreign key ?



Set of properly implemented triggers will do.
If you really want to go for complete extravaganza...
w00t

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2685 Visits: 3494
Is this a theoritical question? It seems odd that the same field would reference two tables, which means that the same data point has to be true in two different entities. Can you fill in the blanks?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search