Recent Posts

Sunday, 19 June 2016

Constraints Tutorial


     Constraint is the mechanism which is used to restrict the invalid data which is enters by the end user by implementing business rules we can apply the constraints on two sitchuation on the table.
1. During the creation of the table.
2. After creation of the table.
Constraints have two names
1. System defined Constraints names
2. User defined constraints names

1. System defined Constraint names
     A constraint name which is defined by the system internally those constraints can be called as system defined constraint names. System defined constraint names are always in the format of SYS_CN. Here n represents any no.

2. User defined Constraints
     The constraint name which is defined by the user manually those means those names can be called as user defined constraint names. We can apply constraints in two levels.
1. Column level constraints
2. Table level constraints

1. Column level Constraints
     Applying the constraints after defining the column immediately those constraints can be called as column level constraints.

2. Table level constraints
     Applying the constraints after defining all the columns in the table or at the end of the table those constraints can be called as table level constraints. Constraints are classified into six types.
1. Unique 
2. Not null
3. Primary key
4. Check
5. Foreign key
6. Default

Unique constraint 
1. This constraint doesn’t allow us to enter duplicate values of a particular column in the table.
2. We can apply constraint more than one column in the table.
3. We can apply this constraint more than one column in the same table.
4. This constraint allows us to enter null values.

1. Unique key
     Unique key is a constraint. It never allows duplicate values. But it allows null values.
We can give constraint in two levels
1. Table level constraints.
2. Column level constraints.

Example for Column level constraints
create table work1 (empno number(10) unique, ename varchar(10), sal number(10));
Example for Table level constraints
create table work2(empno number(10),ename varchar(10), sal number(10), unique(empno));
Constraint violation
unique constraint (SCOTT.SYS_C005251) violated.

2. Not Null
     Not null is a constraint which never allows null values but allows duplicates. We can apply this constraint in column level only.
Column level Example
create table work3 (empno number(10) not null, ename varchar(10), sal number(10));
Table level Example
create table work4 (empno number(10) ,ename varchar(10),sal number(10) not null);

3. Primary key
     It never allows duplicates and null values.
Column level
create table work6 (empno number(10) primary key,ename varchar(10),sal number(10));
Table level
create table work7 (empno number(10),ename varchar(10),sal number(10), primary key(empno));

4. Check
     Which is used to check particular constraint.
E.g
create table work8 (empno number(10),ename varchar(10),sal number(10) check (sal=3000);

5. Foreign key Constraint
     Using this constraint we can maintain the relation between the tables with in the database. This constraint can also be called as referential integrity. If there is any dependencies on the parent table that is dept then we are not suppose to delete the parent record from the parent table. If there is no parent values in the parent table. We can’t enter the values within the child table that is employee table.

Super keys and Candidate keys
Definition
     A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

Examples
     Imagine a table with the fields <Name>, <Age>, <SSN> and <Phone Extension>. This table has many possible superkeys. Three of these are <SSN>, <Phone Extension, Name> and <SSN, Name>. Of those listed, only <SSN> is a candidate key, as the others contain information not necessary to uniquely identify records.
E.g
create table child1(no number(10),sal number(10),constraint bhargav foreign key(sal) references Child(no));
Note
1. We should give the foreign key relationship to another table which has already consist primary key.
2. We can give the foreign key relationship to parent table’s primary key initiated column only.

ON DELETE CASCADE
     This option is used to delete the parent “record” from the parent table even though there are some dependencies.
Note
     It is possible to give only at the time of creating foreign key constraint.
E.g
create table baju1 (no number(10), foreign key(no) references baju(no) on delete cascade);

6. Default 
     This constraint is used to give default value into a particular column in the table.
E.g
create table baju2 (no number(10) default 111);
Example for applying the constraint after creating the table
alter table baju1 modify(no number(10) unique);
Note
     Here we can modify previous table constraints only.

Syntax to see the list of the Constraints
select * from user_cosntraints;
Desc user_constraints;
Syntax to see the list of constraints on a particular table
select constraint_name,constraint_type,status from user_constraints where table_name='BAJU1';
CONSTRAINT_NAME          C        STATUS
---------------         ---      --------
SYS_C005281              R       ENABLED
SYS_C005282              U       ENABLED

Create User Defined Constraint names
create table iron(no number(10) constraint rohit primary key,name varchar(10));
select Constraint_name,Constraint_type,status from user_constraints where table_name='IRON';
CONSTRAINT_NAME           C      STATUS
----------------         ---    --------
ROHIT                     P      ENABLED 
Note
     It shows C as constraint for check and not null but first priority for not null.
Syntax to drop/enable/disable the Constraint:
Syntax
ALTER TABLE<Table name> DISABLE/ENABLE/DROP CONSTRAINT CONSTRAINT_NAME
E.g
alter table iron disable constraint rohit;
alter table iron enable constraint rohit;
alter table iron drop constraint rohit;

Composite Primary key
     Composite primary key means share primary key properties to more than one column at a time in the same table.
E.g
create table vinod(no number(10),name varchar(10),sal number(10),constraint pse primary key(no,name,sal));
Note
     Here we can insert same values into columns, different values but do not give null value. But we can give duplicate values.
To copy a table from one table to another table:
Syntax
CREATE TABLE<Target table name> AS SELECT * FROM <Source Table name>
E.g
create table ranga as select * from vinod;
Syntax to copy a table to another table with specific columns only
CREATE TABLE TABLE NAME<Target table name> as select col1,col2,-------,coln from <source table name>
E.g
create table ranga1 as select no,name from vinod;
Copying a table from one table to another table without data
CREATE TABLE<Target table name> as select * from <Source table name> where <false condition>
E.g
create table ranga3 as  select * from vinod where 1=2;
Name       Null?    Type
--------- --------  -----
NO                  NUMBER(10)
NAME                VARCHAR2(10)
SAL                 NUMBER(10)
Copying a table from one table to another table without data with specific columns
Syntax
CREATE TABLE<Target table name> as select col1,col2,-----------,coln from <Source table name> where <false condition>
E.g
create table ranga4 as select no,name from vinod where 1>2;

Insert  the data from one table to another table
Syntax
insert into elora1  select * from elora;
Note
     We can insert values from one table to another table the table contains same structure and same data types. Otherwise we can’t insert.

No comments:

Post a Comment