How do I ensure that some attributes in my table are not duplicated in an already existing table?

Posted on 2007-10-17
Last Modified: 2010-04-21

I have a table like this:

tblRate (PkRateId, UserId, Rate, Start, End, Active, CreatedDate, ModifiedDate)

Now, I want to create the following rules, but don't know how. Please help:

1. the table should reject entries (at the SQL Server level) when there is already a UserId with overlapping Start and End times.
For example, if the table contained:

1, User24, $20, 10/17/2006, 10/17/2008, y, 9/26/2006, 9/28/2007
2, user25, $25, 10/17/2006, null, y, 9/26/2006, 9/28/2007

I could not enter in a new record:
User24, $24, 10/24/2007, 10/25/2007, n, 9/30/2006, 9/30/2007

Because User24 already has an active rate. I should, however, be able to update where the PkRateId is 1 and change 10/17/2006 to 10/25/2007 if I wanted (or any other field for that matter).  

Start Date is always before End Date

EndDate can only be Null for any rate if "Active" is "y"

There can only be one "y" in Active for any one user.

Btw: PkRateId is Primary Key with an identity specification.

Question by:freezegravity

    Author Comment

    "Because User24 already has an active rate. I should, however, be able to update where the PkRateId is 1 and change 10/17/2006 to 10/25/2007 if I wanted (or any other field for that matter).  "

    I meant to say ... Because User24 already has an active rate within the time specified by the new entry. I should, however, be able to enter in more than one rate for a user if the timeperiod's are different.

    LVL 68

    Accepted Solution

    1.  Will require a custom trigger to enforce.

    2.  Add constraint to table:
    CHECK([StartDate] < [EndDate])

    3.  Add constraint to table:
    CHECK([EndDate] IS NOT NULL OR ([EndDate] IS NULL AND [Active]= 'Y')

    4.  Must be enforced via custom trigger -- could be same trigger as used for #1.
    LVL 68

    Expert Comment

    If you would like samples of the trigger, just let me know.

    Author Closing Comment

    Custom triggers does seem to be what might work.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now