CODEWITHSUNDEEP
sqlsql-server
Frank
Frank

Reputation: 3

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

Upvotes: 0

Views: 70

Answers (1)

RITZ XAVI
RITZ XAVI

Reputation: 3799

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)
ButlerID (PK, int, not null)
CustID (PK, int, 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 already have the schema in place, then you will have to alter your table schema.

Use the below query to alter your table schema. 

ALTER TABLE ButlerSchedule
ADD ButlerID INT,
ADD CustID INT,
ADD CONSTRAINT fk_Butler_ButlerSchedule
FOREIGN KEY (ButlerID)
REFERENCES Butler(ButlerID),
ADD CONSTRAINT fk_ButlerCustomer_ButlerSchedule
FOREIGN KEY (CustID)
REFERENCES ButlerCustomer(CustID);

Upvotes: 2

Related Questions