foreign key references multiple tables

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

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

  • 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))

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

  • 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 ?

  • Previous suggestions using two foreign key constraints will do it even though it is a not common design.

  • ...

    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[/url]

  • 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