Post reply

CHECK constraint

  • July 10, 2010 at 9:15 am

    #387322

    Hi, I am new to SQL databases and have been having trouble doing the following. I need to check that the the maximum value in one column of one table does not exceed the maximum value of one column in another table using a CHECK constraints. How do I do that if it does not allow me to use the MAX() function? The error message complains about the use of an aggregate function.

    Help!!!

  • July 10, 2010 at 9:32 am

    #1191141

    Trigger?

    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

  • July 10, 2010 at 9:38 am

    #1191143

    I am able to do it fine with a trigger but I am required to do it with a CHECK.

  • July 10, 2010 at 12:11 pm

    #1191146

    Please don't double post:

  • July 10, 2010 at 12:38 pm

    #1191147

    How about something like:

    select top (1) TheFieldImInterestedIn

    from TheTableIHaveToPerformMagicWith

    order by TheFieldImInterestedIn DESC

    Check constraints can utilize a function, so you might need to embed your max-field-comparison-between-two-fields logic in that.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 10, 2010 at 1:34 pm

    #1191156

    You would actually need two CHECK constraints because this is effectively a constraint on both tables whereas each CHECK constraint can apply only to one table.

  • July 10, 2010 at 1:45 pm

    #1191157

    index_us (7/10/2010)

    I am able to do it fine with a trigger but I am required to do it with a CHECK.

    Why are you 'required' to use a constraint? Is this a homework type exercise?

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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