1. PRIMARY KEY 2. UNIQUE 3. FOREIGN KEY 4. CHECK 5. NOT NULL

PRIMARY KEY: A PRIMARY KEY constraint is a unique identifier for a row within a database table.

Primary key = No duplicates + no nulls

Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.

The primary key constraints are used to enforce entity integrity.

Example:

CREATE TABLE Customer

(ID int PRIMARY KEY,

Last_Name varchar(30),

First_Name varchar(30));

Example for testing primarykey

insert into customer values(10,'Sanjay','Kumar')

Example

insert into customer values(10,'Rajan','Desai')           

UNIQUE: A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered..

UNIQUE=NO DUPLICATES

Example:

CREATE TABLE Customer3

(ID int UNIQUE,

Last_Name varchar(30),

First_Name varchar(30))

Example for testing UNIQUE

insert into customer3 values(10,'Rajan','Desai')

insert into customer3 values(10,'Tarun','Bhargava')

insert into customer3 values(null,'Manmohan','Singh')          

FOREIGN KEY: A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values.

Ø  A foreign key in one table points to a primary key in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Persons" table:

P_Id	P_Name	P_location
1	John	Newjersy
2	Philips	Newyork
3	David	London

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Nt: that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables.

The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Example to persons table:

create table persons

( p_Id int PRIMARY KEY,

P_name varchar(20),

p_location varchar(20))

Example to create oreders table:

CREATE TABLE Orders

(

O_Id int PRIMARY KEY,

OrderNo int,

P_Id int FOREIGN KEY REFERENCES Persons(P_Id)

)

Example for testing:

Insert into persons values(1,'Jhon','NewZersy')

Insert into persons values(3,'David','London')

Insert into persons values(2,'philips','Newyork')

Insert into Orders values(1,77895,3)

Insert into Orders values(2,44678,3)

Insert into Orders values(3,22456,2)

Insert into Orders values(4,24562,1)

Trying to remove dependent table

Delete persons

Delete persons where p_Id=1

Delete persons where p_Id=2

Delete persons where p_Id=3

Insert into persons values(4,'Scott','France')

Delete persons where p_Id=4

Delete orders where O_Id=4

Delete persons where p_Id=1

Delete orders

Delete persons

CHECK: A CHECK constraint is used to limit the values that can be placed in a column.

Example:

CREATE TABLE Customer5

(SID int CHECK (SID > 0),

Last_Name varchar (30),

First_Name varchar(30));

Exampel to check the constraint:

INSERT INTO Customer5 values ('-3','Gonzales','Lynn')

NOT NULL: A NOT NULL constraint enforces that the column will not accept null values.

Example

CREATE TABLE emp1

(empno int,

ename varchar(30),

location varchar(30),

deptno int NOT NULL);

insert into emp1 values(111,'pavan','Hyderabad',30)

Testing the NOT NULL

insert into emp1 values(222,'Santhosh','Vizag',null)

You can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.

Conclusion

Constraints are the built-in mechanism for enforcing data integrity. Using constraints is preferred to using triggers, rules, and defaults because built-in integrity features use much less overhead and perform faster than the ones you can create. When you write your own code to realize the same actions the constraints can make you can make some errors, so the constraints are not only faster, but also are more consistent and reliable. So, you should use triggers and rules only when the constraints do not provide all the needed functionality.