CODEWITHSUNDEEP
sqlsql-serverdatabase-designforeign-keysprimary-key
CiccioMiami
CiccioMiami

Reputation: 8266

Link multiple table field to the same foreign key in TSQL

I do not really know whether this is logically feasible but I have a table:

PERSON: Id (PK), Name, SoldTo (FK), ShipTo (FK), BillTo (FK)

ADDRESS: AddrId (PK), Street, Number, Town, Type

Type in ADDRESS will contain a string describing the address type. I would like to link SoldTo, ShipTo and BillTo to AddrId. Of course they can also point at the same record.

However in SSMS it forbids me to do that.

Anybody knows a workaround or a better way to do it? Thanks

UPDATE

In order to see what I mean with "SSMS forbids me to do that" have a look at the screenshot in the link below. Consider CMF_ContactInfo as ADDRESS and CMF_AccountInfo as PERSON. As you can see I can add ContactId to the relationship just once...

SSMS Tables and Columns Dialog

Upvotes: 1

Views: 544

Answers (2)

remi bourgarel
remi bourgarel

Reputation: 9389

You have to add multiple relationships ! (with different names) Just like if it were different tables.

Upvotes: 2

Andriy M
Andriy M

Reputation: 77737

Don't know how you are trying to do that in SSMS (possibly you are trying some interactive tools, I know little about them), but this is how you can do what you want in plain Transact-SQL:

CREATE TABLE Address (
  AddrId int CONSTRAINT PK_Address PRIMARY KEY,
  Street varchar(50),
  Number int,
  Town varchar(50),
  Type int
);

CREATE TABLE Person (
  Id int PRIMARY KEY,
  Name varchar(100),
  SoldTo int NOT NULL CONSTRAINT FK_Address_SoldTo FOREIGN KEY REFERENCES Address (AddrId),
  ShipTo int NOT NULL CONSTRAINT FK_Address_ShipTo FOREIGN KEY REFERENCES Address (AddrId),
  BillTo int NOT NULL CONSTRAINT FK_Address_BillTo FOREIGN KEY REFERENCES Address (AddrId)
);

Not sure about the actual types, but you get the idea. Also you might want to drop NOT NULL where it is not needed (or add it where necessary).

Upvotes: 4

Related Questions