multiple tables referencing single primary key

• andrew.ingpen 41964

  SSC Journeyman

  Points: 90

  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 🙂

• Thom A

  SSC Guru

  Points: 98029

  May 13, 2019 at 11:54 am

  #3641387

  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

  May 13, 2019 at 2:05 pm

  #3641490

  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

  May 13, 2019 at 2:10 pm

  #3641492

  Thanks a lot - will have a try 🙂

  Andy

• andrew.ingpen 41964

  SSC Journeyman

  Points: 90

  May 13, 2019 at 3:26 pm

  #3641529

  all working as expected - thanks 🙂