multiple tables referencing single primary key

Question

multiple tables referencing single primary key

andrew.ingpen 41964

SSC Veteran

Points: 200
More actions
May 13, 2019 at 11:23 am

#3641375

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

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

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

Thom A SSC Guru Points: 99143 More actions · Answer 1

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

Eric M Russell SSC Guru Points: 125596 More actions · Answer 2

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