删除表所有的约束（系统默认名约束删除）（存储过程）

 

 

---

原文地址：http://akdora.wordpress.com/2007/01/12/to-remove-all-constraints/

译文：2010-08-10

---

 

我在编程的时候认识到，我创建了错误的约束，我发现了两个方法来弥补我的错误

1-删除表，并且重新创建表，我想这不是一个我需要的解决方式

    drop table table_name cascade constraints;

2-如果你的数据库中有很多的约束，你不可能一个一个去删除，约束是那么的长

    alter table table_name drop constraint constraint_name;

---

 

所以，你可以使用以下存储过程

create or replace procedure disable_fk_constraint is

cursor fke_cur is

select table_name, constraint_name

from user_constraints

where constraint_name like '<<some criteria to specify your constraints>>';

exstr varchar2(4000);

begin

  for fke_rec in fke_cur

  loop

    exstr := 'alter table ' || fke_rec.table_name ||

             ' drop constraint ' ||

              fke_rec.constraint_name;

    begin

      execute immediate exstr;

    exception

      when others then

                dbms_output.put_line('dynamic sql failure: ' || sqlerrm);

               dbms_output.put_line('on statement: ' || exstr);    end;

  end loop;

end disable_fk_constraint;

/

 

你也可以在存储过程中使用以下的查询语句

select table_name, constraint_name

from user_constraints

where constraint_name not like 'sys%' and table_name = 'table_name';

---

 

同时，有许多的约束名是以SYS_CXXXXX开头的，就像你在表中指定的非空约束一样

create table ht_projects (

   project_id              integer           not null,

   project_name            varchar2(100)     not null,

   start_date              date              not null,

   target_end_date         date              not null,

   actual_end_date         date

);

如果你想要通过上面的存储过程删除它们，仅仅更改查询语句就可以了

你可以通过以下查询语句查看到所有的约束：

   select table_name,constraint_name from user_constraints where table_name = 'table_name';

---

SQL> SELECT table_name, constraint_name 2 FROM user_constraints 3 WHERE constraint_name LIKE 'HT_%';TABLE_NAME CONSTRAINT_NAME ------------------------------ ------------------------------ HT_PROJECTS HT_PROJECTS_PK HT_PROJECTS HT_PROJECTS_UK HT_PEOPLE HT_PEOPLE_PK HT_PEOPLE HT_PEOPLE_UK HT_PEOPLE HT_PEOPLE_ROLE_CC HT_PEOPLE HT_PEOPLE_ASSIGNMENT_CC HT_ISSUES HT_ISSUES_PK HT_ISSUES HT_ISSUES_STATUS_CC HT_ISSUES HT_ISSUES_PRIORITY_CC 9 rows selected.SQL> drop table ht_people cascade constraints;Table dropped.SQL> SELECT table_name, constraint_name 2 FROM user_constraints 3 WHERE constraint_name LIKE 'HT_%';TABLE_NAME CONSTRAINT_NAME ------------------------------ ------------------------------ HT_PROJECTS HT_PROJECTS_PK HT_PROJECTS HT_PROJECTS_UK HT_ISSUES HT_ISSUES_PK HT_ISSUES HT_ISSUES_STATUS_CC HT_ISSUES HT_ISSUES_PRIORITY_CC SQL> drop table ht_issues;Table dropped.SQL> SELECT table_name, constraint_name 2 FROM user_constraints 3 WHERE constraint_name LIKE 'HT_%';TABLE_NAME CONSTRAINT_NAME ------------------------------ ------------------------------ HT_PROJECTS HT_PROJECTS_PK HT_PROJECTS HT_PROJECTS_UK SQL> SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'ht_projects';no rows selectedSQL> SELECT table_name, constraint_name, validated FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'HT_PROJECTS';TABLE_NAME CONSTRAINT_NAME VALIDATED ------------------------------ ------------------------------ ------------- HT_PROJECTS HT_PROJECTS_PK VALIDATED HT_PROJECTS HT_PROJECTS_UK VALIDATED HT_PROJECTS SYS_C004098 VALIDATED HT_PROJECTS SYS_C004099 VALIDATED HT_PROJECTS SYS_C004100 VALIDATED HT_PROJECTS SYS_C004101 VALIDATED 6 rows selected.SQL> CREATE OR REPLACE PROCEDURE disable_all_constraint IS 2 3 CURSOR fke_cur IS 4 SELECT table_name, constraint_name 5 FROM user_constraints 6 WHERE constraint_name LIKE 'HT_%'; 7 8 ExStr VARCHAR2(4000); 9 10 BEGIN 11 FOR fke_rec IN fke_cur 12 LOOP 13 ExStr := 'ALTER TABLE ' || fke_rec.table_name || 14 ' DROP CONSTRAINT ' || 15 fke_rec.constraint_name; 16 BEGIN 17 EXECUTE IMMEDIATE ExStr; 18 EXCEPTION 19 WHEN OTHERS THEN 20 NULL; 21 END; 22 END LOOP; 23 END disable_fk_constraint; 24 /Procedure created.SQL> SQL> exec disable_fk_constraint;PL/SQL procedure successfully completed.SQL> SELECT table_name, constraint_name, validated FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'HT_ISSUES';no rows selectedSQL> SELECT table_name, constraint_name, invalid FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'HT_PROJECTS';TABLE_NAME CONSTRAINT_NAME INVALID ------------------------------ ------------------------------ ------- HT_PROJECTS SYS_C004098 HT_PROJECTS SYS_C004099 HT_PROJECTS SYS_C004100 HT_PROJECTS SYS_C004101 SQL> SELECT table_name, constraint_name 2 FROM user_constraints 3 WHERE constraint_name NOT LIKE 'SYS%' AND table_name = 'HT_PROJECTS';no rows selectedSQL> SELECT table_name, constraint_name 2 FROM user_constraints 3 WHERE constraint_name LIKE 'SYS%' AND table_name = 'HT_PROJECTS';TABLE_NAME CONSTRAINT_NAME ------------------------------ ------------------------------ HT_PROJECTS SYS_C004098 HT_PROJECTS SYS_C004099 HT_PROJECTS SYS_C004100 HT_PROJECTS SYS_C004101 SQL> spool off