abenage
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
ASKER
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.