MySQL – How to Set Up Multiple Fields as Primary Key

MySQLprimary-key

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?

Best Answer

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);
Related Question