February 12, 2014 at 4:22 am
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.
February 12, 2014 at 4:37 am
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
February 12, 2014 at 5:00 am
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
February 12, 2014 at 5:03 am
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))
February 12, 2014 at 6:11 am
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.
February 12, 2014 at 7:17 am
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 ?
February 12, 2014 at 10:35 am
Previous suggestions using two foreign key constraints will do it even though it is a not common design.
February 12, 2014 at 10:49 am
...
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:
February 13, 2014 at 6:51 am
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?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply