Use of Constraints in MS SQL

SQL constraints are used to specify rules for the data in a table. A constraint is usually associated with a table and is created with a ADD CONSTRAINT SQL statement.

The Syntax of creating CONSTRAINT in a table is

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

In SQL, we have the following constraints:

NOT NULL - Indicates that a column cannot have NULL value.
UNIQUE - Ensures that each row for a column must have a unique value.
PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table.
CHECK - Ensures that the value in a column meets a specific condition.
DEFAULT - Specifies a default value for a column.

For finding constraint in MS SQL,You have to use the following query

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.
TABLE_CONSTRAINTS

For finding constraint for particular table in database

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.
TABLE_CONSTRAINTS WHERE TABLE_NAME='yourtablename';

Now for dropping any constraint you must know the name of the constraint you want to drop,this can be known from the above mentioned query.After you are aware with the name then you can drop the constraint as below.

For dropping any constraint,we can use the following query

ALTER TABLE yourtablename DROP CONSTRAINT yourconstraintname


I hope this article will help you.