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

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.

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