CHECK constraint

Question

CHECK constraint

index_us

SSC Rookie

Points: 39
More actions
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!!!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

index_us SSC Rookie Points: 39 More actions · Answer 2

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

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

Nils Gustav Stråbø

SSChampion

Points: 11259

July 10, 2010 at 12:11 pm

#1191146

Please don't double post:

WayneS SSC Guru Points: 95460 More actions · Answer 4

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

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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