Constraint in SQL Server
What is Constraint?
The concept of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database. Constraints define rules that must be followed to maintain consistency and accurate to date. Constraint can be define while creating a table or after the table is created. For example if user entered a ID of student in student table, if ID exist in student table and user has entered same ID it would be erroneous situation because student ID cannot be same, with the help of constraint we can prevent this by enforcing data integrity on the table .
Kinds of Constraint:
· Primary Key Constraint
· Unique Key Constraint
· Foreign Key Constraint
· Check Constraint
· Default Constraint
· NOT NULL
Primary Key Constraint
A Primary Key Constraint is define on a column or a set of columns whose values uniquely identify all the rows in a table. These columns are referred to as the Primary Key columns. You can create a single Primary Key constraint as part of the table definition when you create a table is created. If a table already exists, you can add Primary Key constraint, provided that no other Primary Key constraint already exists. A table can have only one Primary Key constraint. When create Primary Key on column, The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the Primary Key constraint. If Primary Key constraint is defined on column that already contains data, then the existing data in the column is screened .If any duplicate values are found then the Primary Key constraint rejected. The syntax of applying the Primary Key constraint when creating table is:
In the above example of the StudentDetail table, Primary Key constraint defining on Id column while creating the table and the name of constraint is cpkID.
Unique Key Constraint
Unique Key constraint is similar to the Primary Key constraint it allows only one NULL row. Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table. The Unique constraint is used to enforce uniqueness on non-primary key constraint column. The syntax of applying the Unique Key constraint when creating table is:
In the above example of the StudentDetail table, Unique Key constraint defining on Id column while creating the table and the name of constraint is cukID.
Foreign Key Constraint
Foreign Key Constraint basically is use for remove inconsistency between tables where data is depend on one table to another table. A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the Foreign Key is to ensure referential integrity of the data. The syntax of applying the Foreign Key constraint when creating table is:
In the StudentDetail table column Id is set as Primary Key, therefore you need to set Id column in StudentCourseDetail table as a Foreign key.
A Check Constraint enforces domain integrity by restricting the values to be inserted in a column The Check constraint is used to limit the value range that can be placed in a column. If you define a Check constraint on a single column it allows only certain values for this column. It is possible to define multiple Check constraints on a single column.
A Check Constraint can be specified by using the following keywords:
Ø IN: To specify entered values are in constant expression from list
Ø LIKE: To specify entered values are in certain pattern
Ø BETWEEN: To specify a range of constant expression
The syntax of applying the Check Key constraint when creating table is:
The preceding command creates the StudentDetail table with a Check constraint on the City column. The name of the constraint is chkcity.
The Default Constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. Only one default constraint can be created for a column but the column cannot be an identity column. The user need not insert values for such column. The syntax of applying the Default constraint when creating table is:
The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. it means that if any column set NOT NULL you have to give the value otherwise it will generate error this field cannot blank. The syntax of applying the NOT NULL constraint when creating table is: