How to Disable and Enable All Constraint
Constraints
Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using DML Triggers, rules, and defaults.Classes of Constraints
- NOT NULL specifies that the column does not accept NULL values. For more information.
- CHECK constraints enforce domain integrity by limiting the values that can be put in a column.
- UNIQUE constraints enforce the uniqueness of the values in a set of columns.
- PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table.
- FOREIGN KEY constraints identify and enforce the relationships between tables.
Column and table constraints.
- Constraints can be column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column.
- A table constraint is declared independently from a column definition and can apply to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint.
- For example, if a table has two or more columns in the primary key, you must use a table constraint to include both columns in the primary key.
How to Disable and Enable All Constraint
Disable all table constraints
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL
Enable all table constraints
ALTER TABLE YourTableName CHECK CONSTRAINT ALL
Disable single constraint
ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint
Enable single constraint
ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint
Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Post a Comment