Post reply

Help with Check Constraint

  • January 21, 2009 at 3:55 pm

    #126927

    Hello All,

    I'm trying to create a check constraint that limits inserts where a value is already present in a column AND where a column is null. In my mind it goes something like this:

    CREATE TABLE [dbo].[Detail_Lock](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Detail_ID] [int] NOT NULL,

    [Lock_Date] [datetime] NOT NULL,

    [Unlock_Date] [datetime] NULL,

    CONSTRAINT [CK_RI_Detail_User_Lock_Exists] CHECK(Detail_ID NOT IN (SELECT Detail_ID FROM Detail_Lock WHERE Unlock_Date IS NULL)

    GO

    But I get the error: Subqueries are not allowed in this context. Only scalar expressions are allowed.

    Does anyone know a way I can get around this?

    Thanks!

  • January 21, 2009 at 5:03 pm

    #930792

    i think you'll have to move the logic to an ON INSERT trigger, ;

    this might be a little tricky, considering that a trigger needs to assume multiple rows might exist....

    say 3 rows are inserted in a single statement....do you refuse all the rows in the insert if one row is invalid, or do you insert just the rows that don't break your business logic?

    yeah, more than likely you are doing one row at a time, and the logic is a little easier, simply rollback in the trigger, but you gotta cover all bases.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 21, 2009 at 5:27 pm

    #930801

    I think that trigger should be "all or nothing" Lowell. That's how Constraints work too.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • January 22, 2009 at 12:32 am

    #930905

    Thanks for the replies. Yes, it was an all or nothing deal. I changed it to an INSTEAD OF INSERT trigger which first verified the record, then inserted it if it was valid or raised an error if not. This way I didn't have to put everything in a transaction and roll it back for invalid records. A colleague of mine was convinced I could make this into a constraint, but it seems hopeless. It would be like creating a unique constraint... with exceptions! 😛

    Thanks again,

    Ruprecht

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply