?
Solved

Help with Check Constraint

Posted on 2005-05-05
4
Medium Priority
?
320 Views
Last Modified: 2010-03-19
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      
0
Comment
Question by:abenage
  • 2
4 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13936091
>> 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
 
LVL 1

Author Comment

by:abenage
ID: 13936255
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13936856
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
 
LVL 7

Expert Comment

by:Nievergelt
ID: 13936953
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question