With Check Add Constraint Followed by Check Constraint VS. Add Constraint

WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

The first syntax is redundant - the WITH CHECK is default for new constraints, and the constraint is turned on by default as well.

This syntax is generated by the SQL management studio when generating sql scripts -- I'm assuming it's some sort of extra redundancy, possibly to ensure the constraint is enabled even if the default constraint behavior for a table is changed.

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

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!

What is the use of generated CHECK CONSTRAINT sql

A CONSTRAINT can be disabled, using NOCHECK. This cannot be done when you CREATE the CONSTRAINT, but has to be done explicitly afterwards, such as with the following:

ALTER TABLE dbo.MyTable NOCHECK CONSTRAINT chk_MyTable;

If you disable a constraint and then use the CREATE TO script option, then that would be reflected too. As such SSMS explicitly defines whether the constraint is to be checked or not afterwards. Although yes enforcing the CHECK is redundant, it creates consistent scripts.

Why is SQL Server is adding a check constraint to my nocheck FK constraint?

This is expected behavior.

When you create a constraint using WITH NOCHECK, all that means is that, at the time you ALTER the table and ADD the constraint, the constraint will not be checked to see if the data already in the table conflicts with it.

After that, it becomes a regular constraint like any other. All future INSERT/UPDATES will have to comply with the constraint. So if you refresh your SSMS and Script the table, you will just see the CONSTRAINT without any NOCHECK.

To summarize, NOCHECK only matters while you are altering the table. After that, it has no effect, and the constraint acts the same as if you had not used NOCHECK when you created it.

If you are trying to permanently REMOVE the constraint, then you need to DROP it.

How to add check constraint on a column in table?

The error message is a little badly worded, but isn't saying what you think it's saying.

It's not saying that there's already a constraint with the same name. It's saying that the constraint is being violated. That means that there is data already in the table that doesn't meet the requirements of the new constraint you're trying to introduce.

You could use the NOCHECK option to create the constraint whilst allowing existing data to violate it. But this is frequently the wrong thing to do. It is usually more sensible to fix the existing data.

Specifying NOCHECK means that the constraint can't be used by the optimizer to eliminate redundant actions that the logic of the constraint would preclude.

Unsure of WITH CHECK clause when adding a constraint

The default for creating new check constraints is the WITH CHECK option that will read and evaluate existing values, failing the ALTER if conflicts are found.

You can use WITH NOCHECK option if you need to deploy a check constraint that disregards existing existing bad values and you understand the downsides (see below), which is the default when enabling previously disabled constraints.

Please see the ALTER TABLE reference for details:

WITH CHECK | WITH NOCHECK

Specifies whether the data in the table is or is not validated against
a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not
specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK
is assumed for re-enabled constraints.

If you do not want to verify new CHECK or FOREIGN KEY constraints
against existing data, use WITH NOCHECK. We do not recommend doing
this, except in rare cases. The new constraint will be evaluated in
all later data updates. Any constraint violations that are suppressed
by WITH NOCHECK when the constraint is added may cause future updates
to fail if they update rows with data that does not comply with the
constraint.

The query optimizer does not consider constraints that are defined
WITH NOCHECK. Such constraints are ignored until they are re-enabled
by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

---

Related Topics