NOT NULL Constraint: Ensures that a
column cannot have NULL value.
DEFAULT Constraint : Provides a default
value for a column when none is specified.
UNIQUE Constraint : Ensures that all
values in a column are different.
PRIMARY Key : Uniquely identified each
rows/records in a database table.
FOREIGN Key : Uniquely identified a
rows/records in any another database table.
CHECK Constraint : The CHECK constraint
ensures that all values in a column satisfy certain conditions.
INDEX : Use to create and retrieve data
from the database very quickly.
Let's see how to apply these constraints after
creation of the table. First create table as follows.
Create
Table Test(
ColumnID
int Identity(0,1),
FirstName
varchar(100)
,
LastName
varchar(100)
,
Age
int
)
NOT NULL
Add NOT NULL constraint to LastName Column
with the following command :
alter table test alter column LastName
varchar(100) not null
DEFAULT
Add DEFAULT constraint to FirstName Column
with the following command :
alter table test add default 'nava' for
FirstName
UNIQUE
Add UNIQUE constraint to Age Column with the
following command :
alter table test add unique( Age)
PRIMARY Key
Add PRIMARY Key constraint to ColumnID Column
with the following command:
alter table Test add Primary key(ColumnID)
FOREIGN Key
Add FOREIGN Key constraint to ColumnID Column
with the following command :
ALTER TABLE test ADD FOREIGN KEY (ColumnID )
REFERENCES CUSTOMERS(ID);
CHECK
Add CHECK constraint to Age Column with the
following command :
alter table test add check(Age>20)