Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

foreign key references multiple tables Expand / Collapse
Author
Message
Posted Wednesday, February 12, 2014 4:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:33 AM
Points: 141, Visits: 403
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.
Post #1540629
Posted Wednesday, February 12, 2014 4:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 40,162, Visits: 36,550
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 2008, MVP
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

Post #1540637
Posted Wednesday, February 12, 2014 5:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:33 AM
Points: 141, Visits: 403
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

Post #1540652
Posted Wednesday, February 12, 2014 5:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:33 AM
Points: 141, Visits: 403
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))


Post #1540655
Posted Wednesday, February 12, 2014 6:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:45 AM
Points: 115, Visits: 638
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.



Post #1540685
Posted Wednesday, February 12, 2014 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:33 AM
Points: 141, Visits: 403
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 ?

Post #1540720
Posted Wednesday, February 12, 2014 10:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:45 AM
Points: 115, Visits: 638
Previous suggestions using two foreign key constraints will do it even though it is a not common design.



Post #1540903
Posted Wednesday, February 12, 2014 10:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:09 AM
Points: 2,873, Visits: 5,183

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



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

How to post your question to get the best and quick help
Post #1540912
Posted Thursday, February 13, 2014 6:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:11 PM
Points: 817, Visits: 2,059
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?


Post #1541167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse