oizin oizin - 19 days ago 7
SQL Question

Unable to drop constraint - SQL Server 2008 R2

I created a constraint checking the positivity of column and am unable to drop the constraint, or to be exact I'm not even clear the constraint exists. Attempting to drop the constraint gives an error message:

ALTER TABLE dbo.Test
DROP CONSTRAINT chk_positive;


Msg 3728, Level 16, State 1, Line 1
'chk_positive' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors

But then experimenting and trying to re-add the constraint suggests its already exists

ALTER TABLE dbo.Test
ADD CONSTRAINT chk_positive CHECK (n_example > 0);


Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "chk_positive ". The conflict occurred in database "...", table "dbo.Test", column 'n_example'.

Looking at the results of
SELECT * FROM sys.check_constraints
there is no mention of the
chk_positive
constraint, however other constraints I created are there.

Any idea why this might be occurring? Any help appreciated.

Answer

In SQL Server, the name-space for constraint names is the entire schema, not just the table.

In other words, two tables cannot have the same constraint name. In general, when I name constraints, I include the table name (in your case, chk_test_positive would be a more likely name).

You can use information_schema.table_constraints to see the list of constraints. There should be a chk_positive that is defined on another table.

My advice: Include the table name in the constraint name.