Dealing with Constraints
Introduction
Three type of constraints are discussed here. ie Primary key, Unique and Referential
A Foreign key constraint has to refer to a Primary key constraint of another table
The constraint_type in user_constraints table has value P for Primary key, U for Unique and R for Referential
The following code works for constraints having 4 or less number of columns
Display All Constrainst and Relations
Master table and child tables
-----------------------------
select a.TABLE_NAME||'('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name))||','||
max(decode(c.position,5,c.column_name))||','||
max(decode(c.position,6,c.column_name))||','||
max(decode(c.position,7,c.column_name))||','||
max(decode(c.position,8,c.column_name))||','||
max(decode(c.position,9,c.column_name))||','||
max(decode(c.position,10,c.column_name))||','||
max(decode(c.position,11,c.column_name))||','||
max(decode(c.position,12,c.column_name))||','||
max(decode(c.position,13,c.column_name))||','||
max(decode(c.position,14,c.column_name))||','||
max(decode(c.position,15,c.column_name))||','||
max(decode(c.position,16,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name))||','||
max(decode(d.position,5,d.column_name))||','||
max(decode(d.position,6,d.column_name))||','||
max(decode(d.position,7,d.column_name))||','||
max(decode(d.position,8,d.column_name))||','||
max(decode(d.position,9,d.column_name))||','||
max(decode(d.position,10,d.column_name))||','||
max(decode(d.position,11,d.column_name))||','||
max(decode(d.position,12,d.column_name))||','||
max(decode(d.position,13,d.column_name))||','||
max(decode(d.position,14,d.column_name))||','||
max(decode(d.position,15,d.column_name))||','||
max(decode(d.position,16,d.column_name)),',')||')'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE IN ('P', 'U')
group by a.table_name, b.table_name
order by 1
/
Child tables referencing a master table
---------------------------------------
select b.TABLE_NAME||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name))||','||
max(decode(d.position,5,d.column_name))||','||
max(decode(d.position,6,d.column_name))||','||
max(decode(d.position,7,d.column_name))||','||
max(decode(d.position,8,d.column_name))||','||
max(decode(d.position,9,d.column_name))||','||
max(decode(d.position,10,d.column_name))||','||
max(decode(d.position,11,d.column_name))||','||
max(decode(d.position,12,d.column_name))||','||
max(decode(d.position,13,d.column_name))||','||
max(decode(d.position,14,d.column_name))||','||
max(decode(d.position,15,d.column_name))||','||
max(decode(d.position,16,d.column_name)),',')||') References '||
a.table_name||'('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name))||','||
max(decode(c.position,5,c.column_name))||','||
max(decode(c.position,6,c.column_name))||','||
max(decode(c.position,7,c.column_name))||','||
max(decode(c.position,8,c.column_name))||','||
max(decode(c.position,9,c.column_name))||','||
max(decode(c.position,10,c.column_name))||','||
max(decode(c.position,11,c.column_name))||','||
max(decode(c.position,12,c.column_name))||','||
max(decode(c.position,13,c.column_name))||','||
max(decode(c.position,14,c.column_name))||','||
max(decode(c.position,15,c.column_name))||','||
max(decode(c.position,16,c.column_name)),',')||')'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE IN ('P', 'U')
group by a.table_name, b.table_name
order by 1
/
Disable all Referential constraints
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
DROP Primary Key Constraint
Remember , In order to drop the primary key, all foreign keys referring the table has to be dropped
A PL/SQL code looks better
set serveroutput ON SIZE 1000000
BEGIN
for c1 in(select table_name, constraint_name from user_constraints where constraint_type='P') LOOP
for c2 in(select table_name, constraint_name from user_constraints where constraint_type='R' and r_constraint_name=c1.constraint_name) LOOP
dbms_output.put_line('alter table '||c2.table_name||' drop constraint '||c2.constraint_name||';');
END LOOP;
dbms_output.put_line('alter table '||c1.table_name||' drop constraint '||c1.constraint_name||';');
dbms_output.put_line('---------------------');
END LOOP;
END;
/
Create Foreign Key constraint
select 'alter table '||a.TABLE_NAME||' add constraint '||a.constraint_name||' foreign key('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name)),',')||');'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE='P'
group by a.table_name, a.constraint_name, b.table_name
order by 1
/
Create Primary Key constraints
select 'alter table '||b.table_name||' add constraint '||b.constraint_name||' primary key('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name)),',')||')'
from user_constraints b, user_cons_columns d
where b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and b.CONSTRAINT_TYPE IN('P')
group by b.table_name, b.constraint_name
order by 1
/
Drop and Add Referential constraints for truncating a table
All referential integrity constraints has to be dropped before truncating a table
The following code can be used to generate scripts to
1) Drop foreign key constraints
2) Truncate table
3) Recreate foreign key constraints
set serveroutput ON
set serveroutput ON
declare
p_tn varchar2(30) := 'EMPLOYEES';
BEGIN
for cur1 in(select 'alter table '||TABLE_NAME||' drop constraint '||constraint_name||';' str from user_constraints where constraint_type='R' and r_constraint_name IN (select constraint_name from user_constraints where constraint_type in('P', 'U') and table_name=p_tn)) LOOP
dbms_output.put_line(cur1.str);
END LOOP;
dbms_output.put_line('truncate table '||p_tn||';');
for cur1 IN( select 'alter table '||a.TABLE_NAME||' add constraint '||a.constraint_name||' foreign key('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name)),',')||');' str
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
b.table_name=p_tn and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE='P'
group by a.table_name, a.constraint_name, b.table_name
order by 1) LOOP
dbms_output.put_line(cur1.str);
END LOOP;
END;
/
Create hierarchy for Referential Tables
It is not possible drop a table that is referenced by some other tables.
Before dropping the table, the FK constraint has to be dropped or the FK table has to be dropped.
Also when creating tables the correct table creation order has to be determined
The parent table has to be created before child table.
The following script can be used to arrange the table hierarchially.
with data
as
(select table_name, constraint_name, r_constraint_name, constraint_type,
nvl( (select constraint_name from user_constraints uc2 where uc2.table_name=uc1.table_name and uc2.constraint_type = 'P'),
(select constraint_name from user_constraints uc2 where uc2.table_name=uc1.table_name and uc2.constraint_type = 'U') ) pkey
from user_constraints uc1
where constraint_type in ('P','U','R'))
select rpad('*',2*level,'*')||table_name tname from data
start with table_name not in (select table_name from data where constraint_type = 'R')
connect by prior pkey = r_constraint_name and constraint_type = 'R'
/
Missing rows from child tables
For finding out the rows that are missing in the parent for a Referential constraint column
Create the following table to store the rowid values of the problem rows
create table rows_cons_missing(table_name varchar2(30), constraint_name varchar2(30), missing_row rowid);
--- DELETE rows_cons_missing;
set serveroutput ON
DECLARE
str1 varchar2(1000);
str2 varchar2(1000);
qry1 varchar2(1000);
v_tabname varchar2(30) := 'EMPLOYEES';
rtabname varchar2(30);
BEGIN
for cur1 in(select constraint_name, r_constraint_name from user_constraints where table_name=v_tabname and constraint_type='R')
LOOP
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str1 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.constraint_name and c.constraint_name=cur1.constraint_name;
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str2 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.r_constraint_name and c.constraint_name=cur1.r_constraint_name;
select table_name INTO rtabname from user_constraints where constraint_name=cur1.r_constraint_name;
qry1 := 'INSERT INTO rows_cons_missing select '''||v_tabname||''','''||cur1.constraint_name||''',rowid from '||v_tabname||' where ('||str1||') NOT IN (select ('||str2||') from '||rtabname||')';
--dbms_output.put_line(qry1);
execute immediate qry1;
dbms_output.put_line(sql%rowcount||' Rows inserted for constraint '||cur1.constraint_name);
END LOOP;
END;
/
Missing rows from parent table
While deleting records from the master table there can be an excpetion, because
there are records in the child table referencing the parent table.
The following script can be used to identify the rows thar are currently referenced by other tables
Here again we use the follwing table for storing the rowid values
SQL> desc rows_cons_missing
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
CONSTRAINT_NAME VARCHAR2(30)
MISSING_ROW ROWID
--- DELETE rows_cons_missing;
set serveroutput ON
DECLARE
str1 varchar2(1000);
str2 varchar2(1000);
qry1 varchar2(1000);
v_tabname varchar2(30) := 'WEB_CATEGORY';
rtabname varchar2(30);
BEGIN
for cur1 in(select table_name, constraint_name, r_constraint_name from user_constraints where constraint_type='R' and r_constraint_name IN(select constraint_name from user_constraints where constraint_type='P' and table_name=v_tabname))
LOOP
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str1 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.constraint_name and c.constraint_name=cur1.constraint_name;
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str2 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.r_constraint_name and c.constraint_name=cur1.r_constraint_name;
qry1 := 'INSERT INTO rows_cons_missing select '''||v_tabname||''','''||cur1.constraint_name||''',rowid from '||v_tabname||' where ('||str1||') NOT IN (select ('||str2||') from '||cur1.table_name||')';
--dbms_output.put_line(qry1);
execute immediate qry1;
dbms_output.put_line(sql%rowcount||' Rows inserted for constraint '||cur1.constraint_name);
END LOOP;
END;
/
