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 satify
? 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'