Search

Foreign key constraint violation

/*

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;

/

1 view0 comments

Recent Posts

See All

Get DDL for Oracle object

set feed off set long 1000000 set longchunksize 1000000 set pages 0 set lines 32767 set trimspool on set feed off set wrap on set serveroutput on format wrapped set def off set tab off exec dbms_metad

Cancelled scheduled concurrent requests

This SQL query returns all concurrent requests that were scheduled in the past and are not scheduled currently. It can be used for identifying scheduled requests that failed to be resubmitted. select

From Concurrent to Trace

-- Find running concurrent request's database session SELECT fcr.request_id, ftl.user_concurrent_program_name program_name, v.meaning phase, vs.sid, vs.serial#, v1.meaning status, fu.user_name usernam