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?

score 60 · Accepted Answer · edited Feb 04 '20 at 07:07

60

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

edited Feb 04 '20 at 07:07

Laurenz Albe

51,298
4
39
69

answered Jan 26 '14 at 15:17

Maxime Fouilleul

3,525
22
21

Hi, my db does not let me drop the primary key with the error 'Incorrect table definition; there can be only one auto column and it must be defined as a key') – Dmitriy Grankin May 30 '20 at 07:07
2

so one should ALTER TABLE yourtable MODIFY id INT NOT NULL; first. – Dmitriy Grankin May 30 '20 at 07:12
@DmitriyGrankin Yes. It's work for me – Bang Nguyen Oct 18 '22 at 09:39
Will still be able to select based on either one of the three columns still? – TheRealChx101 Dec 04 '22 at 22:22

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

1 Answers1