[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Aggregate functions in CHECK Constraints.

qwerty2_reverse_iterator

3/15/2007 11:04:00 PM

1) Are aggregate functions allowed in CHECK constraints?
Basically, I would like to use an aggregate function, like COUNT, with a
CHECK constraint for a table. But when I try to do it via MS-SQL Mngmnt
Studio Express, I get an error. Basically I right click on a table in the
design view, choose "Check Constraints" option, and add the following:

COUNT(column_name) < 10

in the "Expression" text box. But I get an error in validation.

2) I have two tables in my database, Table1 and Table2. After the end of
some series of transactions (ie, the series of transactions is to be viewed
as an atomic transaction itself), I want to verify that the number of rows in
Table1 equals the number of rows in Table2. Is there a way I can do this via
constraints? (At first I thought of using the initially deferred construct
but it seems TSQL doesn't support yet.)

Thank you in advance.
4 Answers

Aaron [SQL Server MVP]

3/15/2007 11:26:00 PM

0

You're trying to make sure the table never has more than 10 rows? Or never
has more than 10 rows where *that column* is not null?

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...





"qwerty2_reverse_iterator"
<qwerty2reverseiterator@discussions.microsoft.com> wrote in message
news:83482F80-FC38-4AC5-9AFB-02BCC7696691@microsoft.com...
> 1) Are aggregate functions allowed in CHECK constraints?
> Basically, I would like to use an aggregate function, like COUNT, with a
> CHECK constraint for a table. But when I try to do it via MS-SQL Mngmnt
> Studio Express, I get an error. Basically I right click on a table in the
> design view, choose "Check Constraints" option, and add the following:
>
> COUNT(column_name) < 10
>
> in the "Expression" text box. But I get an error in validation.
>
> 2) I have two tables in my database, Table1 and Table2. After the end of
> some series of transactions (ie, the series of transactions is to be
> viewed
> as an atomic transaction itself), I want to verify that the number of rows
> in
> Table1 equals the number of rows in Table2. Is there a way I can do this
> via
> constraints? (At first I thought of using the initially deferred
> construct
> but it seems TSQL doesn't support yet.)
>
> Thank you in advance.


qwerty2_reverse_iterator

3/15/2007 11:42:00 PM

0

The condition is contrived, but I am trying to say that the table never has
more than 10 rows. *I should have added* that none of the columns of the
table can have null values and I am trying to add the constraint to an
existing empty table, hence, the existing table should not violate the
constraint. (The purpose of doing this is to be able to somehow solve my
question #2).

"Aaron Bertrand [SQL Server MVP]" wrote:

> You're trying to make sure the table never has more than 10 rows? Or never
> has more than 10 rows where *that column* is not null?
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sq...
> http://www.aspfa...
>
>
>
>
>
> "qwerty2_reverse_iterator"
> <qwerty2reverseiterator@discussions.microsoft.com> wrote in message
> news:83482F80-FC38-4AC5-9AFB-02BCC7696691@microsoft.com...
> > 1) Are aggregate functions allowed in CHECK constraints?
> > Basically, I would like to use an aggregate function, like COUNT, with a
> > CHECK constraint for a table. But when I try to do it via MS-SQL Mngmnt
> > Studio Express, I get an error. Basically I right click on a table in the
> > design view, choose "Check Constraints" option, and add the following:
> >
> > COUNT(column_name) < 10
> >
> > in the "Expression" text box. But I get an error in validation.
> >
> > 2) I have two tables in my database, Table1 and Table2. After the end of
> > some series of transactions (ie, the series of transactions is to be
> > viewed
> > as an atomic transaction itself), I want to verify that the number of rows
> > in
> > Table1 equals the number of rows in Table2. Is there a way I can do this
> > via
> > constraints? (At first I thought of using the initially deferred
> > construct
> > but it seems TSQL doesn't support yet.)
> >
> > Thank you in advance.
>
>
>

Razvan Socol

3/16/2007 7:37:00 AM

0

If you want to enforce a business rule that says that a particular
table should not allow more than 10 rows, you can create a trigger,
like this:

CREATE TRIGGER CheckNumberOfRows ON YourTable
FOR INSERT
AS
IF (SELECT COUNT(*) FROM YourTable)>10 BEGIN
RAISERROR('You cannot add more than 10 rows in this table !',16,1)
ROLLBACK
RETURN
END

However, if the number 10 is derived from the content of some other
table, you should create another trigger on that table, too. And if
you want to allow this rule to be temporarily overriden inside a
transaction, but to enforce it when the transaction is committed, I'm
affraid that this would not be possible using triggers or constraints
(you would have to check it explicitly in the procedure that begins
the transaction, and rollback if there are problems).

Razvan

Gert-Jan Strik

3/16/2007 4:29:00 PM

0

Basically, a CHECK constraint can only reference the columns of that
row. Since no other rows can be accessed, no aggregates are possible.
For the same reason you cannot use a CHECK constraint to limit the
number of rows in a table. For that you would need an ASSERTION, which
SQL Server does not support.

You are correct that constraints cannot be deferred in SQL Server. The
same is true for triggers.

As mentioned by others, you could use a trigger to enforce a maximum
number of rows in a table. I don't see a trigger or constraint based
solution for you second requirement though.

HTH,
Gert-Jan


qwerty2_reverse_iterator wrote:
>
> 1) Are aggregate functions allowed in CHECK constraints?
> Basically, I would like to use an aggregate function, like COUNT, with a
> CHECK constraint for a table. But when I try to do it via MS-SQL Mngmnt
> Studio Express, I get an error. Basically I right click on a table in the
> design view, choose "Check Constraints" option, and add the following:
>
> COUNT(column_name) < 10
>
> in the "Expression" text box. But I get an error in validation.
>
> 2) I have two tables in my database, Table1 and Table2. After the end of
> some series of transactions (ie, the series of transactions is to be viewed
> as an atomic transaction itself), I want to verify that the number of rows in
> Table1 equals the number of rows in Table2. Is there a way I can do this via
> constraints? (At first I thought of using the initially deferred construct
> but it seems TSQL doesn't support yet.)
>
> Thank you in advance.