Link multiple table field to the same foreign key in TSQL

Question

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

Why does SSMS forbid you to do this?? Can you show us what you're trying to do (in T-SQL) ?? Do you get errors - if so: what errors exactly?? — marc_s
– marc_s, Commented Dec 7, 2011 at 16:31

Andriy M · Accepted Answer · 2011-12-08 06:27:16Z

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).

+1 exactly - there should not be any problem creating three FK relations from Person to Address using those columns....
+1 to Andriy. My question to @CiccioMiami: It seems like someone is selling, shipping (and billing for) persons. Has slavery been re-legalized in Miami?
Ahaha, good one! It was just an example. See the screenshot in the question update. Thanks

Andriy M · Accepted Answer · 2011-12-08 11:53:03Z

2

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

edited Dec 8, 2011 at 11:53

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered Dec 7, 2011 at 16:33

remi bourgarel

9,4494 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

Link multiple table field to the same foreign key in TSQL

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related