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.
Larnu.uk
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy