SQL CHECK Constraint logic

Question

I have a table called 'DailyAllocations', which has a soft delete flag (DATETIME, NULLABLE) called 'Deleted', and the other significant column called ProcessHistoryID (INT, NULLABLE).

I need to add a contraint to the table, that will not allow a row to be soft deleted, while there is a value in the ProcessHistoryID.

ALTER TABLE dbo.DailyAllocation
ADD CONSTRAINT chk_dont_delete_processed CHECK ((Deleted IS NULL AND ProcessHistoryID IS NULL) OR (Deleted IS NOT NULL AND ProcessHistoryID IS NULL))

But even though my data is valid, it's failing when I try to apply the containt:

The ALTER TABLE statement conflicted with the CHECK constraint "chk_dont_delete_processed". The conflict occurred in database "MyDB", table "dbo.DailyAllocation".

It might be my logic, or else, I am doing something strange.

So, you can't have a deleted row, with a ProcessHistoryID. What am I doing wrong?

The following query returns no results, which means the data is currently clean:

SELECT *  FROM dbo.DailyAllocation WHERE Deleted IS NOT NULL AND ProcessHistoryID IS NOT NULL

Is the table empty? Are you sure none of existing rows violate the given constraint? — zerkms
– zerkms, Commented Apr 14, 2014 at 5:19
Just added a line of code that I use to check that the data is clean - and the result brings back nothing, which means the data is currently valid. — Craig
– Craig, Commented Apr 14, 2014 at 5:21
Have you checked SELECT * FROM dbo.DailyAllocation WHERE Deleted IS NULL AND ProcessHistoryID IS NOT NULL query? — pabdulin
– pabdulin, Commented Apr 14, 2014 at 5:22
I'd say even WHERE ProcessHistoryID IS NOT NULL. The part of constraint related to Deleted is redundant. — zerkms
– zerkms, Commented Apr 14, 2014 at 5:23
Yes, it returns results, which is valid. You should only have a ProcessedHistoryID on non-deleted rows. — Craig
– Craig, Commented Apr 14, 2014 at 5:24

pabdulin · Accepted Answer · 2014-04-14 05:27:58Z

3

It's easier to have this rule NOT (Deleted IS NOT NULL AND ProcessHistoryID IS NOT NULL) - invert result of single not-allowed combination.

answered Apr 14, 2014 at 5:27

pabdulin

35.6k9 gold badges66 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL CHECK Constraint logic

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related