Hello everyone,
I noticed a strange (lack of) error with a check constraint I had on a table in my database and wondered if anybody else has seen this.
Create a simple table as:-
Code:
CREATE TABLE t_batch (
dt_allocated DATETIME NULL,
dt_created DATETIME NOT NULL
)
ALTER TABLE t_batch ADD CONSTRAINT ck_bth_allocated CHECK(dt_allocated >= dt_created)
GO
Now, I would expect that if I INSERT a new row with a NULL value for dt_allocated that the check constraint would complain and stop the INSERT.
Try this:-
Code:
INSERT INTO t_batch
(dt_allocated, dt_created)
VALUES(NULL, GETDATE())
On my setup it works just fine, but if you run the SELECT below it returns no rows.
Code:
SELECT *
FROM t_batch
WHERE dt_allocated >= dt_created
Now I thought this was weird so I check to ensure the check constraint works at all.
Code:
INSERT INTO t_batch
(dt_allocated, dt_created)
VALUES(DATEADD(d, -1, GETDATE()), GETDATE())
This INSERT failed with a check constraint violation.
My @@OPTIONS variable returns 5496 (decimal), 1578 (hex).
My @@VERSION variable returns 'Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) '
Basically, I've installed service pack 4 with the AWE hotfix.
Has anybody seen this, knows of a patch?
Kep.
CHECK Constraint Error
Linear Mode
