Post reply

How to create a check constraint where another table column value is also involved

  • March 12, 2015 at 10:52 am

    #316223

    Table ATable B

    AIDBID

    Col 1BCol1

    Col 2

    Col3

    I want to put a check constraint on A.col3 that depends on the values of A.Col1, A.Col2 and B.BCol1

    Can please some body let me know that how can I achieve this without using function

    Many Thanks

  • March 12, 2015 at 11:00 am

    #1783654

    Wrap the check in a user defined function and call the function in the CHECK constraint

    Gerald Britton, Pluralsight courses

  • March 12, 2015 at 11:14 am

    #1783665

    Hi, I will not be able to put function in the patch, that's why I cant use the function

  • March 12, 2015 at 9:32 pm

    #1783763

    If you cannot use a FUNCTION in your CHECK constraint, then you cannot reference another table in the CHECK constraint. Using a FUNCTION is the only way to achieve this.

  • March 16, 2015 at 3:02 am

    #1784164

    A function will do it, but it can't be trusted because the check constraint on table A won't get checked if the column in table B changes and hence you can have a constraint that's violated with no error, the error will show up the next time any column in Table A is changed, even if it's not related to the constraint

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2015 at 5:30 am

    #1784206

    Triggers might help but could be complex. You would need triggers on both tables to manage/control changes to either table.

  • March 16, 2015 at 5:35 am

    #1784210

    roger.price-1150775 (3/16/2015)

    Triggers might help but could be complex. You would need triggers on both tables to manage/control changes to either table.

    Yes, it would. In a situation like this I'd take the trigger though, as I prefer complex but correct to a constraint that I just can't trust.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 16, 2015 at 6:06 am

    #1784223

    or...

    create stored procedure for maintenance of the two tables and include the necessary check logic in them. Then don't give anyone datawriter. Give them execute on the stored procedures. Not perfect as someone somewhere will have datawriter and ignore the stored procedures.

  • March 16, 2015 at 9:57 pm

    #1784399

    Is it possible that we are overlooking the possibility here that there's a FOREIGN KEY constraint relationship between table A and B.BCol1?

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

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

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