CHECK constraint

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » CHECK constraint

CHECK constraint

Rate Topic

Display Mode

Topic Options

Author

Message

index_us

Posted Saturday, July 10, 2010 9:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, July 10, 2010 1:27 PM
Points: 3, Visits: 3

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!!!

Post #950350

GilaMonster

Posted Saturday, July 10, 2010 9:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 37,688, Visits: 29,947

Trigger?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #950352

index_us

Posted Saturday, July 10, 2010 9:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, July 10, 2010 1:27 PM
Points: 3, Visits: 3

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

Post #950354

Nils Gustav Stråbø

Posted Saturday, July 10, 2010 12:11 PM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:44 PM
Points: 1,786, Visits: 3,323

Please don't double post: [url=http://www.sqlservercentral.com/Forums/Topic950355-374-1.aspx][/url]

Post #950360

WayneS

Posted Saturday, July 10, 2010 12:38 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:38 PM
Points: 6,368, Visits: 8,230

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

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #950362

sqlvogel

Posted Saturday, July 10, 2010 1:34 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 406, Visits: 2,852

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.

David

Post #950372

GilaMonster

Posted Saturday, July 10, 2010 1:45 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 37,688, Visits: 29,947

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 2008, MVP
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

Post #950373

« Prev Topic | Next Topic »

Permissions