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)
Primary
PK_Butler_ButlerID
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)
Primary
PK_ButlerCustomer_CustID
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)
Primary
PK_ButlerSchedule_ScheduleDateTime
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);