multiple tables referencing single primary key

  • andrew.ingpen 41964

    SSC Journeyman

    Points: 90

    Hello - this is such a basic concept but i have never needed to explore it this thoroughly.

    If i have table A with primary key  - i would have thought i can have table B and C each with foreign keys referencing table A's primary id. Doesn't look like this is possible with a direct foreign key constraint?

    Thanks! all comments welcome 🙂

  • Thom A

    SSC Guru

    Points: 98029

    andrew.ingpen 41964 wrote:

    i would have thought i can have table B and C each with foreign keys referencing table A's primary id. Doesn't look like this is possible with a direct foreign key constraint?

    Are you sure? What did you try? This SQL creates the tables fine, and fails the Foreign Key constraints where appropriate:

     

    CREATE TABLE dbo.TableA (ID int IDENTITY,
    SomeVal varchar(10),
    CONSTRAINT PK_aID PRIMARY KEY CLUSTERED(ID));
    GO

    CREATE TABLE dbo.TableB (ID int IDENTITY,
    SomeVal varchar(10),
    fID int,
    CONSTRAINT PK_bID PRIMARY KEY CLUSTERED(ID),
    CONSTRAINT FK_BA FOREIGN KEY (fID) REFERENCES dbo.TableA (ID));
    GO

    CREATE TABLE dbo.TableC (ID int IDENTITY,
    SomeVal varchar(10),
    fID int,
    CONSTRAINT PK_cID PRIMARY KEY CLUSTERED(ID),
    CONSTRAINT FK_CA FOREIGN KEY (fID) REFERENCES dbo.TableA (ID));

    GO

    INSERT INTO dbo.TableA (SomeVal)
    VALUES ('abc'),('xyz');

    INSERT INTO dbo.TableB (SomeVal,
    fID)
    VALUES ('def',1);

    INSERT INTO dbo.TableC (SomeVal,
    fID)
    VALUES ('ghi',1);
    GO
    INSERT INTO dbo.TableB (SomeVal,
    fID)
    VALUES ('def',3);
    GO
    INSERT INTO dbo.TableC (SomeVal,
    fID)
    VALUES ('ghi',4);
    GO

    DROP TABLE dbo.TableC;
    DROP TABLE dbo.TableB;
    DROP TABLE dbo.TableA;

    Can you share your attempt(s) and the error(s) you got?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Eric M Russell

    SSC Guru

    Points: 124938

    Perhaps it's a limitation of the GUI you're using to design your tables. T-SQL certainly allows for declaring the same foreign key in multiple tables.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • andrew.ingpen 41964

    SSC Journeyman

    Points: 90

    Thanks a lot - will have a try 🙂

    Andy

  • andrew.ingpen 41964

    SSC Journeyman

    Points: 90

    all working as expected - thanks 🙂

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply