Constraints

Constraints

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 situation 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 
  • This constraint doesn’t allow us to enter duplicate values of a particular column in the table.
  • We can apply constraint more than one column in the table.
  • We can apply this constraint more than one column in the same table.
  • 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));
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.

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 super key is a combination of attributes that can be uniquely used to identify a database record. A table might have many super keys. Candidate keys are a special subset of super keys 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 super keys. 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;
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.

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 ashok 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 ashok1 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)

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.

Constraints
Scroll to top