'Constraints' are used to set integrity rules to tables and columns.
Create Table
The 'Constraints' are used to check the type of data that goes into a column. This maintains the integrity of
the table. Constraints can be defined in two ways:
- Column Level Constraints - created along with the creation of the column as part of the column creation and
applies only to that column
- Table Level Constraints - created along with the creation of the table and declared independantly of that of
the column. This type of creation is normally done when you create a constraint as a combination of two or more
columns in a table.
SQL 'Constraints' Syntax
CREATE Table tablename
(
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype,
column4 datatype,
.
.
.
columnn datatype
)
The above method of creating a table creates a 'Primary Key' constraint on column1 at the column level as it is
defined as part of a column definition.
CREATE Table tablename
(
column1 datatype,
column2 datatype,
column3 datatype,
column4 datatype,
.
.
.
columnn datatype
CONSTRAINT constraint_name PRIMARY KEY (column1, column3)
)
The above method of creating a table creates a 'Primary Key' constraint on column1 and column3 at the table level
as shown. You give a name to this constraint.
Alter table tablename
Add Constraint Constraintname PRIMARY KEY (column1, column2...);
)
You can even create a constraint after the creation of the table by using the 'Alter' statement at the
table level as shown above. You give a name to this constraint.
Alter table tablename Drop Constraint constraintname
You can drop any of the constraints that you have defined by using the 'Alter Table' command with the 'Drop Constraint' option
as shown above.
In SQL server, the following types of constraints are available:
- Unique Constraints
- Primary Key Constraints
- Foreign Key Constraints
- Check Constraints
- Not Null Constraints
- Default Constraints