Frank Frank - 19 days ago 6
SQL Question

Combining Primary Keys

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

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);
Comments