May 13, 2019 at 11:23 am
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 🙂
May 13, 2019 at 11:54 am
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.
May 13, 2019 at 2:05 pm
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
May 13, 2019 at 2:10 pm
Thanks a lot - will have a try 🙂
Andy
May 13, 2019 at 3:26 pm
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