Including Constraints
constraints
constraints enforce rules at the table level.
Uses
- They can be used to prevent invalid data entry
- They can be enforced whenever a row is inserted,updated or deleted the table.
- They can be used to prevent the deletion of table if there are dependencies from other tables.
The valid data integrity contraints are
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
Defining Constraints
- Constraints can be created while table creation.
- They can also be created after table creation
- Constraints can be given names , otherwise oracle server will generate a name by using
SYS_cn
- Constraints can be created column level or table level.
- 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.
- NOT Null constraint
This constraint ensures that null values are not allowed for the column
- 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))
- 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)
- 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
- foreign key – defines a column in the child table as foreign key
- references – identifies the related column in the parent table
- 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)
- CHECK constraint
- This constraint defines a condition that each row must satisfy.
- 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.
- 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
- we can add or drop a constraint. We cannot modify it
- we can enable or disable a constraint
- we can add a not null constraint by using modify clause of alter table.
- 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
- A constraint can be deactivated by disabling it.
- The cascade option can be used to deactivate the dependant constraints also.
alter table disable constraint constraintname
Enabling a constraint
- The deactivated constraint can be enabled by using Enable clause
- The unique or the primary key is automatically created if the constraint is activated.
alter table enable constraint constraintname
Cascading Constraint
- The cascade constraint clause is used along with drop column clause
- 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’;
- Only the not null constraint is displayed if a table is described.
- Other constraints can be viewed from user_constraints
- constraint types are indicated as
- C- Check constraint
- P – Primary key
- R – Referential
- U – Unique
To view the columns associated with the constraints
select constraint_name,column_name from user_cons_columns where table_name=’emp’