How to set up multiple fields as primary key in MySQL?

Question

I have a table with fields

EmployeeID

blahblah

blahblah2

.....

RecordMonth

RecordYear

• so each employee should only have a matching entry for a month, year, Emp#. How do I set up a table.

So how do I set up the table so that EmployeeID can be updated once a month but can never have two entries for a matching month and year?

Answer 1

Use a compound primary key:

CREATE TABLE yourtable 
  ( 
     employeeid  INT, 
     blahblah    VARCHAR(255), 
     blahblah2   VARCHAR(255), 
     recordmonth DATE, 
     recordyear DATE, 
     PRIMARY KEY (employeeid, recordmonth, recordyear) 
  ) 

And if your table already exists, drop the old primary key:

ALTER TABLE yourtable
DROP PRIMARY KEY;

And recreate it:

ALTER TABLE yourtable
ADD PRIMARY KEY (employeeid, recordmonth, recordyear);