Help with Check Constraint

I have an employment table which has person_id(int) and a current_company(bit) fields.  How can I create a check constraint to ensure that the current_company bit set to 1 is unique for a person.  This table basically holds employment history, so there may be multiple companies for a given person, but only one of them can be the current company.

To guarantee that this does not happen:

Person_id      Company_id      Current_Company      
12345            5345            1      
12345            678            1      
12345            345            0      
12345            789            0      
12345            436876            0      
12345            8766            0      
12345            76809            1      
12345            5757            0      

This is correct:
Person_id      Company_id      Current_Company      
12345            5345            0      
12345            678            0      
12345            345            0      
12345            789            0      
12345            436876            0      
12345            8766            0      
12345            76809            1      
12345            5757            0      
LVL 1
abenageAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> How can I create a check constraint to ensure that the current_company bit set to 1 is unique for a person. <<

Don't think you can.  A check constraint can only look at a single row.  You will need a trigger to do this, I think.


>> This table basically holds employment history, so there may be multiple companies for a given person, but only one of them can be the current company. <<

Are they always put in in order?  That is, if an INSERT comes in with company set to 1 can all other companies for that person be set to 0?  If not, is there some type of date/counter in the row, so that one could logically determine which row is the "active"/current one?
0
 
abenageAuthor Commented:
if an INSERT comes in with company set to 1 can all other companies for that person be set to 0?

I think that will be the best way to handle it.  I guess it is best to keep the data integrity checks on the db rather than doing it through code.  I don't have any experience with triggers, so I'll check it out.
0
 
Scott PletcherSenior DBACommented:
OK, something like this then:

CREATE TRIGGER employment_trg_ins
ON employment
AFTER INSERT
AS
UPDATE employment
SET current_company = 0
FROM employment
RIGHT OUTER JOIN inserted i ON i.person_id = employment.person_id AND i.current_company = 1
WHERE employment.company_id <> i.company_id
AND employment.current_company = 1
0
 
NievergeltSenior SW DevCommented:
I agree with ScottPletcher, a trigger is the way to ensure that such a condition does not occur.

Here it is a possible trigger to ensure your condition:

CREATE TRIGGER Single_Employment ON [dbo].[employment_history]
AFTER INSERT, UPDATE
AS
  DECLARE @multipleEmployments int
  SELECT @multipleEmployments = COUNT(*) FROM
    (SELECT COUNT(*) multiple FROM
      (SELECT * FROM [dbo].[employment_history]
       UNION
       SELECT * FROM inserted) u
     WHERE current_company = 1
     GROUP BY person_id
     HAVING (COUNT(*) > 1)) r
  IF (@multipleEmployments > 0)
  BEGIN
     RAISERROR ('A person may only be employed by one company.', 16, 1)
     ROLLBACK TRANSACTION
  END

However, a trigger will not correct your data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.