Definition of
Constraint
It is the set of restrictions for data while storing and retrieving
from DB and maintains the Data Integrity. I.e. It ensures the consistency and
correctness of data stored in the database.
Constraint Categories
- Entity Integrity – Unique, Primary key
- Domain Integrity – Not null, Check
- Referential Integrity – Foreign key
Principle of Constraints
- Can create constraints to ensure data integrity.
- Constraints define routed that must be followed to maintain consistency and correctness of data
Enforcement of Constraints
- Constraints can be enforced in two levels
- Column Level
- Table Level
Constraints construction
- Constraints can be created either by using the following statements,
- Create Table
- Alter Table
Syntax: - Using CREATE
CREATE TABLE table name
(column name CONSTRAINT constraint_name Constraint_type[Cons_name, Cons_type].
Syntax: - Using ALTER
ALTER TABLE table name
ADD CONSTRAINT constraint name constraint type (condtion)
With Check /with no Check
- Specifies whether the existing data has to be check or not to check
for a newly added constraint or a renamed constraint.
1. Entity Integrity Constraint
This Constraint ensures that each row can be uniquely identified by an
attribute known as Primary key or Unique Key.
I.
PRIMARY
KEY (Not Null + Unique)
·
A Primary key
constraint is define on a column or a set of columns to create identity
rows in a table
·
Primary key column can’t contain null values,
since it is used to uniquely identify rows in a table.
·
If a primary key constraint is defined on a
column that already contains data then existing data in the column is checked for
any duplicate values. If any duplicates found then the primary constraint will
be rejected.
Syntax:
CONSTRAINT constraint name PRIMARY
KEY (column name 1, column name 2..)
Example 1:
CREATE TABLE Emp (Eid Char(4) CONSTRAINT Eid_PK PRIMARY KEY)
Example 2:
ALTER TABLE Emp ADD CONSTRAINT Eid_PK PRIMARY KEY(Eid).
II.
UNIQUE
·
UNIQUE is used to enforce to apply uniqueness on
non Primary key columns
·
It allows NULL Values
·
Multiple unique constraints can be created on a
table.
Syntax:
CONSTRAINT constraint name UNIQUE
(column name 1, column name 2..)
Example 1:
CREATE TABLE Emp SSN Char(25) CONSTRAINT cons_SSN UNIQUE
Example 2:
CREATE Table Emp(Empid numeric(5),Email Varchar(25)) Constraint Email_uk
UNIQUE(Email))
2. Domain Integrity Constraint
This Constraint ensures that only range of values to be stored in a
column and It enforces by restricting the type of data, range of values, and
format of the data.
I.
NOT
NULL
Ensures the column contains no NULL values, by default the table can
contains NULL values and it can be able to specified only at the column level
not at the table level.
Example
CREATE TABLE Emp ( Eid no(6),Last Name Varchar (25) NOT NULL, Salary
no(8,2), PRIMARY KEY(Eid))
II.
CHECK
·
CHECK constraint enforces domain integrity by restricting
the values inserted in a column.
·
It is possible to define a multiple CHECK constraints
on a single column.
·
A single CHECK constraint can be applied to
multiple columns when it is defined at the table level.
Syntax:
CONSTRAINT constraint name CHECK
(expression)
Example:
CREATE TABLE Emp (salary numeric (8,2,) CONSTRAINT sal_CK CHECK(Salary
> 1500))
3. Referential
Integrity (RI)
It ensures that the values of the foreign key matches with the values of
the corresponding primary key
I.
Foreign
Key
The key is used to remove the inconsistency in two tables when data in
the one table depends on the data in the another table
Syntax:
CONSTRAINT constraint name Foreign
Key (column name) REFERENCE table name (column name)
Example:
CREATE TABLE Emp (Eid numeric(8,2), Did numeric(8,2) CONSTRAINT Did_fk REFERENCE
Dept(Did))
II.
DEFAULT
Default constraint can be used to assign a constant value to a column and
there is no need for user input for a specified column
Syntax:
CONSTRAINT constraint name DEFAULT
(constant expression/NULL)
Example:
CREATE TABLE Emp (City Char(15) DEFAULT ‘New York’)
