Constraints:

By using constraints we can verify the data correctness or we can validate the incoming data. 1. Primary key 2. Unique constraint 3. Foreign key 4. Check 5. Default 6. Not null

Not Null Constraint:

i. When we apply not null constraint on any column, it will not accept null values.

Example:

create table Employees
(
    eid int,
    name varchar(40) not null,
    salary int
)
Insert into employees values (1,'rajeev',20000)
Insert into employees values ('kishore') [parse error]
Insert into employees (name) values ('kishore')
Insert into employees (name, salary) values ('veena',12000)
Employees
eidnamesalary
1Rajeev20000
nullrkishorenull
nullveena12000

Default constraint:

i. Default constraint will insert default values into the columns on which default constraint is specified, when user is not passing data into the column. ii. Defaults can be like that evaluates to a constant, built in function or mathematical expression.

Example:

create table patient 
(
   Pid patient,
   Name varchar(40) not null,
   City varchar(40) default 'Bangalore'
)
Insert into patient values (1, 'raju');  [parse error]
Insert into patient (pid, pname) values(1,'raju');
Insert into patient (pid,pname,city) values(2, 'mahesh','mysure');
Insert into patient values (3, 'rajeev', 'hosur');
pidpnamecity
1Rajubangalore
Note: Sometimes we can use computed values as default values.

Check constraint:

i. Enforces domain integrity, by limiting the range of values into a column. ii. Domain integrity can be achieved.

Example:

create table employees
(
  Eid int,
  Name varchar(40).
  Age in years int check (age in years between 20 and 60)
)
a) Only if the inserted values are correct, they are inserted into columns. b) More than one constraint can be applied to a given column. Note: By using constraints present in SQL, we can validate the data against simple business rules. (We cannot use SQL constraints for validating or for checking complex business rules).

Primary key constraint:

i. It enforces the entity integrity (not allowing multiple rows to have same identity within table) ii. It will give uniqueness to the rows. iii. It will not allow duplicate values. iv. It can be applied on one or more columns.(when it is applied for more than one column, primary key constraint is called as composite primary key) v. Only one primary key constraint is allowed within the table.

Example:

create table patients
(	
    Name varchar(40),
    Dob date,
    Bg varchar(40),
    Disease varchar(40),
    Constraint pk -name-dob primary key(name,dob)
 )
Insert into patients (name,dob,bg,disease) values ('rajeev','10-10-1998,o+ve,'malaria');
Insert into patients (name,dob,bg,disease) values ('kishore','10-10-1999,b+ve,'typhoid');
Insert into patients (name,dob,bg,disease) values ('rajeev','10-10-1998,o+ve,'malaria');[duplication so truncated]

Unique constraint:

i. It is same as primary key but it will allow one null value into its columns. ii. More than one unique constraint can be created in a table.

Example:

create table trainers
       (
          Tid int primary key,
          Name varchar(40).
          Email varchar(40) unique,
          cellnum varchar(40)
)
Insert into trainers (tid,name,email,cellnum) values (1, 'lok', 'lok@palle.com', 9848830919);
Insert into trainers (tid,name,email,cellnum) values (2,'prashanth',null,null);
Note: -> In terms of data restriction wise unique and not null constraints combination is equal to primary key constraint. -> primary key constraint and unique, not null constraint combination is not exactly equal, since database engine will create clustered index on unique constraints columns.

Foreign Key Constraint:

i. By using this key we can acheive referential integrity. Referential integrity means we can able to limit the values of a tables's particular column values if the values are present in another referring table.

Example:

create table authors
(
aid int primary key,
aname varchar(20),
cityid int foreign key references cities(cid) on delete 
);