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