21

I have a table with fields

EmployeeID

blahblah

blahblah2

.....

RecordMonth

RecodrdYear

  • 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?

42

Use a compound primary key:

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

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

protected by Community Apr 12 at 7:58

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.