Constraint
Constraint is a rule or restriction that apply on column.
Different type of constraint
· Primary key
· Foreign key
· Default
· Check
· Null/Not null
Primary key: it ensure unique value in column with not null value.
Foreign key: foreign key are used to enforce database integrity, a foreign key in one table point primary key in other table. The value we entered in the foreign key column should be one of the value of primary key.
Add foreign key constraint in a specified table:
Alter table table_name add constraint foreignkeytablename_foreignkeycolumnname
_FK foreign key (foreignkeycolumnname) references primarykeytablename (pkcname)
Default constraint:-
If doesn't give value then value will be add automatically by default constraint.
Syntax: - for existing column
Alter table table name add constraint DF_tablename_columnname Default (value) for column_name.
Syntax:-for new column
Alter table tablename add column_name constraint constraint name default (value)
Syntax for drop constraint:-
Alter table table_name drop constraint name
Check constraint:-
It is used to specify some condition to inserting value into column ex:-age>=0, if we use null value for comparison then it will return unknown not null.
Syntax of check constraint:-
Alter table table_name add constraint constraint name check (column_name Boolean expression value).
Drop constraint:-
Alter table table_name drop constraint constraint name
Note: Alter table doesn't allow not null when add new column it will allow 0nly when we provide default value (by use of default constraint).
Unique key:-
We use unique key constraint to enforce uniqueness of column, it doesn't allow duplicate value
Difference between unique key and primary key
Table can have only one primary key but more than one unique key
Unique key allow null but primary key doesn’t allow.
Syntax of add unique key constraint:-
Alter table table_name
Add constraint constraint_name
Unique (email)
Drop unique key
Drop constraint constraint_name.