/*
This PL/SQL script checks violations of foreign key constraints
Example of the tables:
CREATE TABLE temp_supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);
CREATE TABLE temp_products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES temp_supplier(supplier_id, supplier_name)
);
alter table temp_products disable constraint fk_supplier_comp;
insert into temp_supplier values (1, 'Supplier 1', 'Test 1');
insert into temp_products values (10, 1, 'Supplier 1');
insert into temp_products values (20, 2, 'Supplier 2');
*/
set serveroutput on size 1000000
declare
type myrowids is record (rid ROWID);
type rowidtab is table of myrowids;
my_violations rowidtab;
m_sql varchar2(32000) := '';
m_cnt number := 0;
begin
-- Main loop over all base tables
for my_constraints in (
select distinct b.owner base_owner, b.table_name base_table, c.owner constr_owner, c.table_name constr_table, a.constraint_name
from all_cons_columns b,
all_cons_columns c,
all_constraints a
where b.constraint_name = a.constraint_name
and a.owner = b.owner
and b.position = c.position
and c.constraint_name = a.r_constraint_name
and c.owner = a.r_owner
and a.constraint_type = 'R'
and b.table_name in ('TEMP_PRODUCTS') -- Add here other base tables
order by b.owner, b.table_name)
loop
dbms_output.put_line('Table : ' || my_constraints.base_owner || '.' || my_constraints.base_table);
dbms_output.put_line('Constraint: ' || my_constraints.constraint_name || chr(13) || chr(10));
-- Constract dynamic SQL
m_sql := 'select b.ROWID myrowid from ' ||
my_constraints.base_owner || '.' || my_constraints.base_table || ' b ' ||
' where not exists (select 1 from ' || my_constraints.constr_owner || '.' || my_constraints.constr_table || ' c ' ||
' where ';
-- Inner loop over all table's foreign key's conditions
for my_conditions in (
select my_constraints.base_owner,
my_constraints.base_table,
b.column_name base_column,
' b.' || b.column_name || ' = ' ||
' c.' || c.column_name || decode(b.position, 1, ')', ' and ') text
from all_cons_columns b,
all_cons_columns c,
all_constraints a
where b.constraint_name = a.constraint_name
and a.owner = b.owner
and b.position = c.position
and c.constraint_name = a.r_constraint_name
and c.owner = a.r_owner
and a.constraint_type = 'R'
and b.owner = my_constraints.base_owner
and b.table_name = my_constraints.base_table
and a.constraint_name = my_constraints.constraint_name
order by b.position desc)
loop
-- Append condition to dynamic SQL
m_sql := m_sql || my_conditions.text;
end loop;
dbms_output.put_line(m_sql);
dbms_output.put_line('These ROWIDs will violate the ' || my_constraints.constraint_name || ' constraint: ' || chr(13) || chr(10));
execute immediate m_sql bulk collect into my_violations;
for i in my_violations.first..my_violations.last
loop
dbms_output.put_line(to_char(my_violations(i).rid));
end loop;
end loop;
end;
/