Constraints

Including Constraints

constraints
constraints enforce rules at the table level.
Uses

  1. They can be used to prevent invalid data entry
  2. They can be enforced whenever a row is inserted,updated or deleted the table.
  3. They can be used to prevent the deletion of table if there are dependencies from other tables.

The valid data integrity contraints are

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

Defining Constraints

  1. Constraints can be created while table creation.
  2. They can also be created after table creation
  3. Constraints can be given names , otherwise oracle server will generate a name by using

SYS_cn

  1. Constraints can be created column level or table level.
  2. Constraints are stored in the data dictionary and they can be viewed from it.

syntax
create table (colname datatype [column_constraint],…. [table_constraint][,…])

eg
Table level constraint
create table emp(empno number,ename varchar2(30),deptno number not null,constraint empno_cons primary key(empno))

References one or more columns and is specified  seperately from column definitions. Not null constraint cannot be given.

Column level constraint

create table emp(empno number constraint empno_con primary key,ename varchar2(20),deptno)

References single column and is defined with the column itself. Any constraint can be given.

  1. NOT Null constraint

This constraint ensures that null values are not allowed for the column

  1. UNIQUE constraint

This integrity constraint ensures that every value in a column or set of columns be unique. ie no two rows of a table have duplicate values in a speicified column or set of columns.This constraint accepts null values for the columns.

eg
create table dept(deptno number,dname varchar2(20),loc varchar2(20),constraint cons_dept unique(dname))

  1. PRIMARY KEY constraint

This constraint creates a primary key for a table. There can be only one primary key for a table. This constraint enforces uniqueness of a column and also it does not contain null values.

eg
create table emp(empno number primary key,empname varchar2(30)

  1. FOREIGN KEY constraint

This is also called as referential integrity constraint. It establishes a relationship between a primary key or unique key in the same table or different table. We can create a table name employee in which the deptno field acts as a foreign key or referential key to the dept table. The employee table is called as dependant or child table. The dept table is called as referenced or parent table.

eg

create table emp(empno number,empname varchar2(30),deptno  number(2),constraint d foreign key(deptno) references dept(deptno))

keywords

  1. foreign key – defines a column in the child table as foreign key
  2. references – identifies the related column in the parent table
  3. on delete cascade – allows deletion in the parent table and deletion of dependant rows in the child table.If this keyword is not given the deletion of record in the parent table is not allowed.

eg

create table emp(empno number,empname varchar2(30),deptno number(2) constraint dpt references depart(deptno) ON DELETE CASCADE)

  1. CHECK constraint
  1. This constraint defines a condition that each row must satisfy.
  1. Expressions that are not allowed are
  • References to currval,nextval,level and rownum pseudocolumns.
  •  calls to sysdate,uid,user
  •  queries that refer to other values in other rows.
  1. There can be any no. of check constraints for a column.

eg

… deptno number(2), constraint ck check(deptno between 10 and 20)

Adding a constraint

  1. we can add or drop a constraint. We cannot modify it
  2. we can enable or disable a constraint
  3. we can add a not null constraint by using modify clause of alter table.
  4. we can define not null constraint only if the table contains no rows, because data cannot be specified for existing rows at the same time of column addition

syntax

alter table add [constraint constraintname] type (column)

eg

alter table emp add constraint mgr_const foreign key(mgr) references emp(empno)

Dropping a constraint

We can drop a constraint by identifying it by its name. The cascade clause can be used to  drop any related constraints also

alter table drop constraint constraintname [ cascade]

If the constraint is not named then

alter table drop constrainttype [cascade]

Disabling a constraint

  1. A constraint can be deactivated by disabling it.
  2. The cascade option can be used to deactivate the dependant constraints also.

alter table disable constraint constraintname

Enabling a constraint

  1. The deactivated constraint can be enabled by using Enable clause
  2. The unique or the primary key is automatically created if the constraint is activated.

alter table enable constraint constraintname

Cascading Constraint

  1. The cascade constraint clause is used along with drop column clause
  2. This clause is used to drop all the referential integrity constraints to the primary key constraint column while deleting the column

alter table drop(PRIMARYKEYCOLUMNNAME)  cascade constraint

Viewing the constraints

constraints can be viewed by querying the USER_CONSTRAINTS table

select constraint_name,constriant_type from user_constraints where table_name=’emp’;

  1. Only the not null constraint is displayed if a table is described.
  1. Other constraints can be viewed from user_constraints
  1. constraint types are indicated as
  1. C- Check constraint
  2. P – Primary key
  3. R – Referential
  4. U – Unique

To view the columns associated with the constraints

select constraint_name,column_name from user_cons_columns where table_name=’emp’