I added two fields, which either both needed to be null or both filled in. (Now, it is possible this requirement points to a normalisation requirement - but that's not the point at hand). So the check constraint is simply to ensure that either:
- Field A is null AND Field B is null
- Field A is not null AND Field B is not null
So I used the following check constraint:
USE tempdb
CREATE TABLE AllOrNothing
(
id INT IDENTITY(1, 1) ,
FieldA VARCHAR(100) ,
FieldB VARCHAR(100)
)
ALTER TABLE AllOrNothing ADD CONSTRAINT UK_AllOrNothing
CHECK (
(FieldA IS NULL AND FieldB IS NULL)
OR
(FieldA IS NOT NULL AND FieldB IS NOT NULL)
)
Now, what surprised me was when I queried sys.constraints on this:
and again if I modify the constraint in SQL Server Management Studio:
The brackets have gone! Now, this isn't going to affect the functionality due to the precedence order of the logical operators, but will still have a SQL Developer looking at it and wondering if the intended functionality is what's been put in place. (Of course, the best way to be sure is to unit test your code).
I thought I'd blog about this, as whilst it doesn't change the function of what's been coded, it does change the form of it, which is unusual and was to me unexpected.
No comments:
Post a Comment