转自 http://www.gold98.net/blog/article.asp?id=125
不能删除包含主键的行,该主键被用做另一个表的外键
Error: orA-02292: integrity constraint <constraint name> 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 have 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 and then you can delete the corresponding value into the parent table.
For example, if you had created the following foreign key (parent-child relationship).
Create TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
Create TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier (supplier_id)
);
Then you try inserting into the products table as follows:
Insert INTO supplier
(supplier_id, supplier_name, contact_name)
VALUES (1000, 'Microsoft', 'Bill Gates');
Insert INTO products
(product_id, supplier_id)
VALUES (50000, 1000);
Then you tried to delete the record from the supplier table as follows:
Delete from supplier
Where supplier_id = 1000;
You would receive the following
error message:
Since the supplier_id value of 100 exists in the products, you need to first delete the record from the products table as follows:
Delete from products
Where supplier_id = 1000;
Then you can delete from the supplier table:
Delete from supplier
Where supplier_id = 1000;
不能删除包含主键的行,该主键被用做另一个表的外键
完整性约束错误
如果你尝试删除一条记录,该记录中的值依赖一个完整性约束,一个错误被返回。
某例子试图从DEPARTMENTS表中删除部门号60,但执行该语句将返回一个错误,因为部门号在EMPLOYEES表中被用做外键。如果你试图删除的父记录有子记录,那么,你将收到child record found violation orA-02292。
下面的语句可以正常工作,因为在部门70中没有雇员:
Delete FROM departments
Where department_id = 70;
1 row deleted.
注释
如果使用了引用完整性约束,当你试图删除一行时,你可能收到一个Oracle服务器错误信息。但是,如果引用完整性约束包含了ON Delete CASCADE选项,那么,可以删除行,并且所有相关的子表记录都被删除。