1 What are Constrains
1) Constrains enforce on the table level
2) Constrains the deletion of a table if there are dependencies
2 Constrain Guidelines
1) Name a constraint or the oracle generate a name by the sys_cn format
2) Create a constraint either
--At the same time as the table is created.or
--After the table has been created
3)Define a constraint at the column or table level
4)view constraint in the data dictionary
3 Crete a constraint
create table test2
(id int not null,-- column level
lname varchar(20),
fname varchar(20),
constraint uk_test2_1 unique(lname,fname))--table level
4 The not null Constraint
create table employees(
employee_id number(6),
last_name varchar2(25) not null --system named
hire_date DATE
constraint emp_hire_date not null --User named
5Foreign key
create table test3
(rid int,
name varchar(30),
constraint fk_test3_1 foreign key(rid) reference test2(id));
froeign key constraint keywords
foreign key :Define the column in thee child table at the table constrain level.
references :Identifies the table and column in the parent table.
on delete cascade: Delete the dependent rows in the child table when a row in the parent table is deleted
on delete set null:Convert the dependent foreign key values to null when a row in the
parent table is deleted.
--parent table referenced table
--child table refernce other table
6 The check Constraint
Define a condition that each row must be satisfy
alter table test3
add constrain ch_test3 check(name like 's%')
7 Dropping a Constraint
1) Remove the manager constraint form the employee table
alter table test3
drop constriant test3_manager_fk
2) Remove the primary key constraint on the departments table and drop the associated
foreign key constraint on the employees.department_id column
alter table departments
drop primary key cascade
8 Disabling and enable Constraints
1)Execute the disable clause of the alter table statment to deactive an integrity constraint
2)Apply the cascade option to disable dependent integrity constrints
alter table employees
disable constraint emp_emp_id_pl cascade
3) enabling Constraints
.Active an integrity constraint currently disabled in the table definition by using the enable clause.
alter table employees
enable constraint emp_emp_id_pk;
a unique or a primary index is automatically created if you enable a unique key or a primary key constraint
8 View Constraints
select constraint_name,constriant_type,serch_condition
from user_constraints
where table_name='employees'
9 view the columns associated with constraints
select constraint_name,column_name
from user_cons_columns
where table_name='employees'