Infolinks

Saturday 12 May 2012

SQL-CONSTRAINTS


CONSTRAINTS

Constraints are categorized as follows.

Domain integrity constraints
ü  Not null
ü  Check

Entity integrity constraints
ü  Unique
ü  Primary key

Referential integrity constraints
ü  Foreign key

Constraints are always attached to a column not a table.
We can add constraints in three ways.

ü  Column level -- along with the column definition
ü  Table level                 -- after the table definition
ü  Alter level                  -- using alter command

While adding constraints you need not specify the name but the type only, oracle will internally name the constraint.
If you want to give a name to the constraint, you have to use the constraint clause.

NOT NULL

This is used to avoid null values.
We can add this constraint in column level only.

Ex:
     SQL> create table student(no number(2) not null, name varchar(10), marks
             number(3));

     SQL> create table student(no number(2) constraint nn not null, name varchar(10),
             marks number(3));

CHECK

This is used to insert the values based on specified condition.
We can add this constraint in all three levels.

Ex:
     COLUMN LEVEL

     SQL> create table student(no number(2) , name varchar(10), marks number(3) check
             (marks > 300));
      SQL> create table student(no number(2) , name varchar(10), marks number(3)
             constraint ch  check(marks > 300));

      TABLE LEVEL

      SQL> create table student(no number(2) , name varchar(10), marks number(3), check
             (marks > 300));
      SQL> create table student(no number(2) , name varchar(10), marks number(3),
             constraint ch check(marks > 300));

      ALTER LEVEL

      SQL> alter table student add check(marks>300);
      SQL> alter table student add constraint ch check(marks>300);


UNIQUE

This is used to avoid duplicates but it allow nulls.
We can add this constraint in all three levels.




Ex:
      COLUMN LEVEL

     SQL> create table student(no number(2) unique, name varchar(10), marks
             number(3));
      SQL> create table student(no number(2)  constraint un unique, name varchar(10),
             marks number(3));

       TABLE LEVEL
     
      SQL> create table student(no number(2) , name varchar(10), marks number(3),
             unique(no));
      SQL> create table student(no number(2) , name varchar(10), marks number(3),
             constraint un unique(no));

      ALTER LEVEL

      SQL> alter table student add unique(no);
      SQL> alter table student add constraint un unique(no);


PRIMARY KEY

Ø  This is used to avoid duplicates and nulls. This will work as combination of unique and not null.
Ø  Primary key always attached to the parent table.
Ø  We can add this constraint in all three levels.

Ex:
      COLUMN LEVEL

      SQL> create table student(no number(2) primary key, name varchar(10), marks
             number(3));
      SQL> create table student(no number(2)  constraint pk primary key, name varchar(10),
             marks number(3));
     


      TABLE LEVEL

      SQL> create table student(no number(2) , name varchar(10), marks number(3),
             primary key(no));
      SQL> create table student(no number(2) , name varchar(10), marks number(3),
             constraint pk primary key(no));

      ALTER LEVEL

      SQL> alter table student add primary key(no);
      SQL> alter table student add constraint pk primary key(no);


FOREIGN KEY

Ø  This is used to reference the parent table primary key column which allows duplicates.
Ø  Foreign key always attached to the child table.
Ø  We can add this constraint in table and alter levels only.

Ex:
      TABLE LEVEL

     SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),
             primary key(empno), foreign key(deptno) references dept(deptno));
      SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),
             constraint pk primary key(empno), constraint fk foreign key(deptno) references
             dept(deptno));

      ALTER LEVEL
    
      SQL> alter table emp add foreign key(deptno) references dept(deptno);
                SQL> alter table emp add constraint fk foreign key(deptno) references dept(deptno);

Once the primary key and foreign key relationship has been created then you can not remove any parent record if the dependent childs exists.

No comments:

Post a Comment