Home Code Snippets Oracle Reference Oracle Functions Oracle Error Codes Forum Oracle Jobs Oracle Blogs

Dealing with Constraints

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;
/

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

Post a Comment

You must be logged in to post a comment.