Dropping/Disabling Check Constraints and NOT NULL constraints in SQl Server

The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database. For instance, we have foreign key constraints to ensure all orders reference existing products. You cannot enter an order for a product the database does not know about. Maintaining integrity is of utmost importance for a database, so much so that we cannot trust users and applications to enforce these rules by themselves. Once integrity is lost, you may find customers are double billed, payments to the supplier are missing, and everyone loses faith in your application. We will be talking in the context of the SQL Server sample Northwind database.
Data integrity rules fall into one of three categories: entity, referential, and domain.

Check Constraints
Check constraints contain an expression the database will evaluate when you modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. You can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.

NOT NULL Constraints
A logically unknown expression happens when a NULL value is present in an expression.

Any SQL INSERT, UPDATE or DELETE command applied to a table with constraints enabled has the possibility of failing.

For example, updates applied to a Parent Table may fail if the statement leaves orphaned rows in a child table, INSERT command against a Child Table may fail if a matching foreign key value does not exist in the parent table.
Constraint failures will result in the statement being rolled back - coding an application front end to deal with such errors is generally easier than handling all the business rules in code.


Some applications require to ignore check constraints and NOT NULL constraints , arguing that the business logic of an application should be retained within the business logic layer of an application. However, in such a scenario, it becomes possible for invalid data to be added to the database. So we need to disable or drop these constraints.

Following is a one line command can do a thing like this:

-- Drop Check constraints
select 'ALTER TABLE ' + s.name+'.'+OBJECT_NAME(o.parent_object_id) + ' DROP CONSTRAINT ' +OBJECT_NAME(OBJECT_ID)
FROM sys.objects as o
join sys.schemas as s on s.schema_Id = o.schema_id
WHERE o.type_desc LIKE '%CHECK_CONSTRAINT'

-- Drop NOT NULL constraints on column and and allow NULL

select 'ALTER TABLE ' + s.name+'.'+o.name + ' ALTER COLUMN ' +c.name + ' '+ t.name +' NULL'
FROM sys.objects as o
join sys.schemas as s on s.schema_id=o.schema_id
join sys.syscolumns as c on o.name=object_name(c.id)
join sys.systypes as t on c.usertype=t.usertype
where o.type='U'
and c.isnullable=1