Oracle删除约束

删除一个表某条记录时，出现如下错误：
ORA-02292: integrity constraint (CICRO.FK3_EL_NODES) violated - child record found
于是查看了相关的主键和约束关系，发现了一些问题。[@more@]

关于这个错误，oracle官方解决方法是：
Error: orA-02292: integrity constraint violated - child record found

Cause: You tried to Delete a record from a parent table (as referenced by a foreign key), but a record in the child table exists.

Action: The options to resolve this oracle error are:
This error commonly occurs when you ha a parent-child relationship established between two tables through a foreign key. You then have tried to delete a value into the parent table, but the corresponding value exists in the child table.
To correct this problem, you need to update or delete the value into the child table first an

总结一语句话，就是：

不能删除包含主键的行，该主键被用做另一个表的外键。

解除方法：
1. 用下面方法找出关联的约束:
SQL> select a.constraint_name||'|'||a.table_name||'|'||b.constraint_name
2 from user_constraints a, user_constraints b
3 where a.constraint_type = 'R'
4 and b.constraint_type = 'P'
5 and a.r_constraint_name = b.constraint_name
6 and (a.table_name = 'EL_APPLICATIONS' OR b.table_name = 'EL_APPLICATIONS')
7 ;

A.CONSTRAINT_NAME||'|'||A.TABLE_NAME||'|'||B.CONSTRAINT_NAME
--------------------------------------------------------------------------------
FK3_EL_WORK_NODES|EL_WORK_NODES|PK_EL_APPLICATIONS
FK3_EL_NODES|EL_NODES|PK_EL_APPLICATIONS
FK1_EL_LIBS_IN_APPS|EL_LIBS_IN_APPS|PK_EL_APPLICATIONS
FK1_EL_APPLICATION_PARAMS|EL_APPLICATION_PARAMS|PK_EL_APPLICATIONS
FK1_EL_APPLICATION_MESSAGES|EL_APPLICATION_MESSAGES|PK_EL_APPLICATIONS
FK1_EL_APPLICATION_FILES|EL_APPLICATION_FILES|PK_EL_APPLICATIONS
FK1_EL_APPLICATION_DOCS|EL_APPLICATION_DOCS|PK_EL_APPLICATIONS
FK1_EL_APPLICATION_AUDITKEYS|EL_APPLICATION_AUDITKEYS|PK_EL_APPLICATIONS

8 rows selected.

2. 由于用不了Toad,只好直接在Sqlplus操作了;由于是Product环境的数据,需要慬慎,在删除之前要先检查数据是否符合我要删的数,就写了一个Script来导出数据.
#!/bin/perl

use strict;
use warnings;

open ( my $fh, "desc.sql" ) || die "1 Can not open $!n";
open ( my $fw, ">unload2.sql" ) || die "1 Can not open $!n";

my $header = "
set head on
set verify off
set trimspool on
set newpage 0
set pagesize 0
set lines 200
set termout off
set serveroutput off
set feedback off
set echo off
set colsep |

spool ./unload_cfg_909.txt;

";

print $fw $header;

my $sql;
my $table;
my $fg = 0;
my $lfg = 0;
my $key;

while ( my $re = ) {
chomp $re;
##print "[A]$ren";

if ( $re =~ /^SQL> desc ([wd]+)/ ){
##print "[B] $ren";

$fg = 1;
$lfg = 1;

$table = $1;
$sql = "SELECT ";

print $fw "n--&gt[C] Table: $tablen" if defined $table;
print "--&gt[C] Table: $tablen" if defined $table;
}

next if $re =~ /^ Name/;

my $column;
if ( $re =~ /^ ([w]+)/ ) {
##print "[D] Column: $1n";
$column = $1;

if ( $column =~ /VERSIONID$/ ){
$key = $column ;
}
if ( $lfg == 0 ) {
$sql = $sql . "||'|'||" . $column;
}
else {
$sql = $sql . $column;
}

$lfg = 0;

##print "[E] sql = ". $sql . "||'|'||" . $column ."n";

}

if ( ($re =~ /^SQL> $/) && ($fg == 1) ){
##print "[B] $ren";
print "SELECT $table FROM DUAL;n";

print $fw "SELECT '--$table--' FROM DUAL;n";
print $fw "$sql FROM $table WHERE $key = 909;n";
print "$sql FROM $table WHERE $key = 909;n";

$fg = 0;

}

}

print $fw "
spool off;

exit;";
close($fh);
close($fw);

3.确认数据没问题后,用下面语句删除数据,先commit,是为了出错rollback;删除后,要是没有问题再commit提交.
commit;

delete from EL_APPLICATION_PARAMS WHERE APPVERSIONID = 38;

delete from EL_APPLICATION_MESSAGES WHERE APPVERSIONID = 38;

delete from EL_APPLICATION_FILES WHERE APPVERSIONID = 38;

delete from EL_APPLICATION_DOCS WHERE APPVERSIONID = 38;

delete from EL_LIBS_IN_APPS WHERE APPVERSIONID = 38;

delete from EL_NODES WHERE APPVERSIONID = 38;

delete from EL_APPLICATIONS WHERE APPVERSIONID = 38;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/640706/viewspace-1057457/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/640706/viewspace-1057457/