Add Check constraints in SQL Server: ADD CONSTRAINT vs ADD CHECK

Question

I want use a check constraint for a column in any table in SQL Server 2008.

I would like give a qualified name to the check constraint.

I have seen several syntax version on how to create it:

ALTER TABLE [dbo].[Roles2016.UsersCRM] WITH CHECK 
    ADD CHECK (([Estado]=(4) OR [Estado]=(3) OR [Estado]=(2) OR [Estado]=(1)))

ALTER TABLE [dbo].[Roles2016.UsersCRM] WITH CHECK 
    ADD CONSTRAINT [CK_UsuariosCRM_Estado] 
        CHECK (([Estado]=(4) OR [Estado]=(3) OR [Estado]=(2) OR [Estado]=(1)))

What's difference ADD CHECK and ADD CONSTRAINT for a check constraint?

The second syntax allows you to name your constraint. the first doesn't. It's good practice to name constraints. — Zohar Peled, Jul 07 '16 at 11:42

score 3 · Accepted Answer · answered Jul 07 '16 at 11:44

It is possible, but a very bad habit to add constraints without a name:

CREATE TABLE tbl(SomeColumn VARCHAR(10) DEFAULT('test'))

will create a CONSTRAINT with a random name. Better use this

CREATE TABLE tbl(SomeColumn VARCHAR(10) CONSTRAINT DF_YourTable_SomeColumm DEFAULT('test'))

This will do the same, but will name the constraint like you want it.

This is extremely important if you run upgrade scripts in deployed environments. Just imagine, you want to change a constraint later and the name of this constraint is all different on your customers machines... That's a real pain!

So: Always name your constraints!

Add Check constraints in SQL Server: ADD CONSTRAINT vs ADD CHECK

1 Answers1