Hello All

[tekpr00]

Hello All,

I am trying to truncate a table, however, I was getting error:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

So, I decided to disable all constraints with this code.
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;

After disabling all the constraints

I am still getting: ORA-02266: unique/primary keys in table referenced by enabled foreign keys

So I decided to look at child tables of my parent table to see if foreign key constraint is enabled on the child tables. I used this code:
SELECT DECODE(c.status,'ENABLED','C','c') t,
SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname,
SUBSTR(p.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p,
all_constraints c
WHERE c.owner = upper('CRACPT')
AND c.table_name = upper('LEGAL_ENTITIES')
AND c.constraint_type = 'R'
AND p.owner = c.r_owner
AND p.constraint_name = c.r_constraint_name
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
UNION ALL
SELECT DECODE(c.status,'ENABLED','P','p') t,
SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname,
SUBSTR(c.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p,
all_constraints c
WHERE p.owner = upper('CRACPT')
AND p.table_name = upper('LEGAL_ENTITIES')
AND p.constraint_type in ('P','U')
AND c.r_owner = p.owner
AND c.r_constraint_name = p.constraint_name
AND c.constraint_type = 'R'
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
ORDER BY 1, 4, 2, 3;


I got these result (partial)

C LE_HO_PROV_FK HO_PROV_CODE PROVINCES
C LE_MAIL_PROV_FK MAIL_PROV_CODE PROVINCES
C LE_RECORD_PROV_FK RECORDS_PROV_CODE PROVINCES

So, I decided to disable some of these foreign key constraints on the child table.

alter table PROVINCES disable constraint LE_HO_PROV_FK;
alter table PROVINCES disable constraint LE_MAIL_PROV_FK;
alter table PROVINCES disable constraint LE_RECORD_PROV_FK;


I got the constraints does not exist.

Could anyone suggest what I am doing wrong…..

Remember, I am only trying to truncate the parent table.

 

[Dagon]

I notice that in the first query you use user_* but in the second you use all_* tables ? Does that mean that the second query is being run by a different user. If so, you may have to prefix the table name in the drop commands with the owner name.

As to why all the constraints have not been dropped by the main script, that may be errors being reported by the script that you are not seeing. You've done a "set termout off", so you won't see any error messages reported by the script. Also, you seem to be disabling all constraints (including primary key and unique) rather than just foreign key. This seems to be something of an overkill.

 

[tekpr00]

Just before you replied me I figure out my error. Guesss what my error is what you metioned in your first paragraph. I was running the sidable with different user.

Again thanks

Tayo