Post reply

Conditional Check Constraints?

  • April 5, 2005 at 8:41 am

    #66754

    Is there a way to create conditional check constraints?  I have tried many variations of the following constraint and none seem to work:

    CASE WHEN Price <> 0 THEN Status = 'For Sale' END

    Thanks in advance. RH

  • April 5, 2005 at 8:44 am

    #550541

    Price = 0 or Price 0 AND Status = 'For Sale'

  • April 5, 2005 at 8:50 am

    #550547

    Thanks for the reply Remi.  Let me restate my logic - I think it was flawed.  I have multiple conditions I would like to protect.  Can I accomplish this in check constraints?

    WHEN Price IS NULL THEN Status = 'For Lease'

    WHEN Price = 0 THEN Status = 'For Lease'

    WHEN Price > 0 THEN Status = 'For Sale'

    I expect my web developers will protect this in the front-end data entry page - but I want to be sure.

    Thanks in advance.  RH

  • April 5, 2005 at 9:10 am

    #550564

    First of all I'd use a look up table for the status info (assuming you could have a 3rd or 4th status eventually), or simply a bit/tinyint column instead of a varchar if you're 1000000% sure that you're never gonna have more status.

    2nd it'd look something like this :

    --put the most likely case first

    PRICE > 0 AND STATUS = 1 -- for sale

    OR

    ISNULL(Price, 0) = 0 AND STATUS = 0 --for lease

  • April 5, 2005 at 9:29 am

    #550578

    Thanks Remi.  Unfortunately, I am still getting the same error trying to add this as a "constraint expression" in Enterprise Manager.  The error I am getting is:

    "Error validating constraint ck_buildings.  Do you want to edit the constraint?"

    Do I need to change the syntax of your statement in some way?

  • April 5, 2005 at 9:35 am

    #550580

    Here's a similar exemple I created on ony of my tables

    FkQuestion > 0 AND FAIT = 1

    OR

    ISNULL(FkQuestion, 0) = 0 AND FAIT = 2

    Maybe you're still using a varchar column for the status and that is causing problem with my int values.

  • April 5, 2005 at 9:41 am

    #550581

    Remi - it worked like a charm.  My last error was based on my own typing mistake.

    Thanks for your advice and help!

    Ryan

  • April 5, 2005 at 9:50 am

    #550583

    HTH.

  • April 6, 2005 at 12:37 pm

    #550905

    Yes, you're right.  I use CASE all the time in SELECT statements and until you said something, I didn't realize how poor my CASE syntax was.

    There is a big difference between:

    ALTER TABLE dbo.buildings

    ADD CONSTRAINT RyanTest1

    CHECK(CASE WHEN Price > 0 THEN 'For Sale' END=StatusID)

    and...

    ALTER TABLE dbo.buidlings

    ADD CONSTRAINT RyanTest2

    CHECK(CASE WHEN Price > 0 THEN StatusID = 'For Sale' END)

    Obviously, RyanTest1 worked the way I needed it to.  I'm not sure why I even tried RyanTest2.

    Thanks for the response.

Viewing 9 posts - 1 through 8 (of 8 total)

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