How to implement a constraint over multiple rows

Question

How to implement a constraint over multiple rows

Tom257

SSChampion

Points: 11543
More actions
October 13, 2011 at 3:27 am

#260923

I have a contraint working well, where the sum of two column values must not exceed a constant.
Requirements have change. Now the constraint must include multiple rows.
USE TempDB
GO
CREATE TABLE ConstraintCheck (
pKey INT IDENTITY(1,1),
DDate smalldatetime,
Dtype smallINT,
Value1 smallInt,
Value2 smallInt )
-- Existing Constraint
ALTER TABLE ConstraintCheck
ADD CONSTRAINT chkTotalValues CHECK ((Value1 + Value2) <= 999);
INSERT INTO ConstraintCheck (DDate, Dtype, Value1, value2)
SELECT '2011-10-12', 1, 599, 0 UNION ALL
SELECT '2011-10-12', 2, 300, 200 UNION ALL
SELECT '2011-10-13', 1, 0, 600 UNION ALL
SELECT '2011-10-13', 2, 300, 0;
-- Constraint works. this update fails
UPDATE ConstraintCheck
SET Value1 = 1000
WHERE DDate = '2011-10-12'
-- However the requirement change means the constraint should be across all types
-- for a particular day. Here DayTotal must not exceed 999
SELECT DDate, SUM(Value1 + Value2) AS DayTotal
FROM ConstraintCheck
GROUP BY DDate
How can I implement this constraint?

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

As far as I know, a constraint only applies to a single row. In order to do what you require, I think you'll need to use a trigger.

John

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

You can use an INDEXED VIEW and a UNIQUE constraint.

See my blog here[/url].

-- Gianluca Sartori

Tom257 SSChampion Points: 11543 More actions · Answer 3

Thanks,

Interesting blog. I've tried to created the indexed view as I was hoping to avoid the trigger route (for reasons expressed in Gianluca's blog), however I'm getting errors in creating one.

CREATE TABLE dbo.TwoRows( N INT NOT NULL PRIMARY KEY );

INSERT INTO TwoRows VALUES(1);

INSERT INTO TwoRows VALUES(2);

GO

CREATE VIEW dbo.vCHECK_Constraints

WITH SCHEMABINDING

AS

SELECT 1 AS ONE

FROM dbo.ConstraintCheck AS C

CROSS JOIN dbo.TwoRows AS TR

GROUP BY DDate

HAVING SUM(C.Value1 + C.Value2) > 999

GO

CREATE UNIQUE CLUSTERED INDEX IX_vCHECK_Constraints

ON dbo.vCHECK_CONSTRAINTS(ONE);

Error is

"Msg 10121, Level 16, State 1, Line 1

Cannot create index on view "tempdb.dbo.vCHECK_Constraints" because it contains a HAVING clause. Consider removing the HAVING clause."

But If I change the HAVING to WHERE I get another error

"Msg 147, Level 15, State 1, Line 1

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Is there a way around this

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Darn! I guess you're out of luck.

I tried in many ways, but I think that this particular requirement can't be implemented with an indexed view.

Some restrictions apply, and one of those is the absence of the HAVING clause.

I'm sorry for suggesting a wrong way.

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 5

you guys completely forgot that you can use a check constraint that uses a user defined function...and that UDF can check mutiple columns, rows, values, other tables, whatever.

i've done several posts that did things like guarantee that all rows for a particualr Id did not sum up more than 100, etc.

here's an example based off the original tables:

USE TempDB

GO

CREATE TABLE ConstraintCheck (

pKey INT IDENTITY(1,1),

DDate smalldatetime,

Dtype smallINT,

Value1 smallInt,

Value2 smallInt )

-- Existing Constraint

GO

--create a UDF to use for the constriant!

CREATE function dbo.Check999Values(@Value1 smallInt, @Value2 smallInt)

RETURNS smallint

AS

BEGIN

RETURN

CASE

WHEN (@Value1 + @Value2) <= 999

THEN 1

ELSE 0

END

END --Function

GO

ALTER TABLE ConstraintCheck

ADD CONSTRAINT chkTotalValues CHECK (dbo.Check999Values(Value1,Value2)=1);

INSERT INTO ConstraintCheck (DDate, Dtype, Value1, value2)

SELECT '2011-10-12', 1, 599, 0 UNION ALL

SELECT '2011-10-12', 2, 300, 200 UNION ALL

SELECT '2011-10-13', 1, 0, 600 UNION ALL

SELECT '2011-10-13', 2, 300, 0;

-- Constraint works. this update fails

UPDATE ConstraintCheck

SET Value1 = 1000

WHERE DDate = '2011-10-12'

/*

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with the CHECK constraint "chkTotalValues". The conflict occurred in database "tempdb", table "dbo.ConstraintCheck".

The statement has been terminated.

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tom257 SSChampion Points: 11543 More actions · Answer 6

Guess I'm resigned to writing the trigger. Thats not the problem, its justifying it to the DBA thats going to be the problem.

Still I learned something reading the article on indexed views, so thanks for that. 😀

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Lowell (10/13/2011)
you guys completely forgot that you can use a check constraint that uses a user defined function...

No, I'm not forgetting, I'm deliberately avoiding that. 😛

From a performance standpoint, it can be very inefficient. Also, has an hidden pitfall: if the column(s) you modify are not explicitly used in the constraint definition, the constraint is not evaluated at all, even if it is not bound to a particular column (hence, as MS says, it is "table-scoped").

I blogged about it here[/url].

-- Gianluca Sartori