Composite foreign keys

Question

This comes up a lot for me. In SQL Server 2008, I have 3 tables. 2 with unique keys and 1 which is a mapping between them. Example:

People    Events   Schedule
------    ------   --------
PersonId  EventId  ScheduleId
Name      Place    PersonId
                   EventId
                   Rsvp

ScheduleId isn't needed if I make a composite key. I know how to make a composite key like this

ALTER TABLE Schedule ADD CONSTRAINT CK_Schedule_PersonId_EventId
UNIQUE NONCLUSTERED (PersonId, EventId)

but I don't know how to make one that also maps correctly to the foreign keys. How can I do this? Also, if I'm wrong and the ScheduleId way is preferred, please say why.

Answer 1

I will not use composite key in this case due to scalability of database. Suppose if you have 6 foreign keys in your table(Schedule) and ScheduleId is being used in some other tables than I will not use all the 6 foreign keys in upcoming tables. I will try to use ScheduleId as my foreign key.

Answer 2

ScheduleId is usually preferred for ORMs and it gives an absolutely unique and unchangeable primary key that represents a record. Primary keys should not change. Also it makes dealing with records a bit more easy. You just need to give the id for an update or delete instead of passing in a composite identifier.

You can create the foreign key when you are doing the definition for Schedule:

PersonId int FOREIGN KEY REFERENCES People(PersonId)

or

CONSTRAINT fk_PersonId FOREIGN KEY (PersonId) REFERENCES People(PersonId)

or if you are altering an existing table

ALTER TABLE Schedule ADD CONSTRAINT fk_PersonId
FOREIGN KEY (PersonId) REFERENCES People(PersonId)

And I'll mention that if you do make a composite fk then you should make it the primary key to ensure that it is not only unique but not null.

CONSTRAINT pk_Person_Event PRIMARY KEY (PersonId, EventId)