Combining Primary Keys

Question

I have created a Database with 3 tables.

My question is how do I make the Primary Key for the ButlerSchedule table a combination of ButlerID, CustID, and ScheduleDateTime?

I beleive that I need a Foreign Key in ButlerSchedule that references the Primary Keys from the other two tables, but I'm unsure on how to accomplish this task. Thanks.

I'm using SQL-Server

1. Butler

ButlerID (PK, int, not null)



ButlerModel (varchar (70), null)



ButlerName (varchar (70), null)



ButlerType (varchar (70), null)



ButlerMadeOnDate (date, null)



ButlerActive (bit, null)



ButlerOutOfServiceDate (date, null)

The

Primary

Key is

PK_Butler_ButlerID

2. ButlerCustomer

CustID (PK, int, not null)



CustFName (varchar (70), null)



CustLName (varchar (70), null)



CustAddress (varchar (70), null)



CustCity (varchar (70), null)



CustState (varchar (70), null)



CustZip (char (10), null)



CustCurrent (bit, null)



CustPITA (bit, null)

The

Primary

Key is

PK_ButlerCustomer_CustID

3. ButlerSchedule

ScheduleDateTime (PK, date, not null)



PaidStatus (char (1), null)



CompletedStatus (char (1), null)



ButlerReview (varchar (max), null)



CustReview (varchar (max), null)



EnteredDate (date, null)



ModifiedDate (date, null)

The

Primary

Key is

PK_ButlerSchedule_ScheduleDateTime

Answer 1

Add this to your ButlerSchedule table schema.

ButlerId int FOREIGN KEY REFERENCES Butler(ButlerId)
CustId int FOREIGN KEY REFERENCES ButlerCustomer(CustId)

So now, your BustlerSchedule schema will look like this.

ScheduleDateTime (PK, date, not null)
PaidStatus (char (1), null)
CompletedStatus (char (1), null)
ButlerReview (varchar (max), null)
CustReview (varchar (max), null)
EnteredDate (date, null)
ModifiedDate (date, null) 
ButlerId int FOREIGN KEY REFERENCES Butler(ButlerId)
CustId int FOREIGN KEY REFERENCES ButlerCustomer(CustId)

If you want to alter your table, use the below command.

ALTER TABLE ButlerSchedule
ADD CONSTRAINT fk_Butler_ButlerSchedule
FOREIGN KEY (ButlerId)
REFERENCES Butler(ButlerId),
ADD CONSTRAINT fk_ButlerCustomer_ButlerSchedule
FOREIGN KEY (CustId)
REFERENCES ButlerCustomer(CustId);

Combining Primary Keys

Sign up