关于这个错误:
ORA-02449, 00000, "unique/primary keys in table referenced by foreign keys"
// *Cause: An attempt was made to drop a table with unique or
// primary keys referenced by foreign keys in another table.
// *Action: Before performing the above operations the table, drop the
// foreign key constraints in other tables. You can see what
// constraints are referencing a table by issuing the following
// command:
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
我们删除一个表时,但是该表A的主键列或者唯一性列被引用为其他表B的外键,此时我们无法删除A
下面是以删除表空间users:
SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
SQL> alter table hr.employees drop CONSTRAINT emp_dept_fk;
表已更改。
通过下面的查询可以找出HR用户下所有表的主外键约束(已经确认只有hr用户使用users表空间)
select * from dba_constraints where constraint_type='F' AND OWNER='HR';
select * from dba_constraints where constraint_type='P' AND OWNER='HR';
下面删除了部分约束:
SQL> alter table hr.gongsi drop CONSTRAINT pk3;
表已更改。
SQL> alter table hr.worker drop constraint PK1;
表已更改。
SQL> alter table hr.deparment drop constraint PK2;
alter table hr.deparment drop constraint PK2
*
第 1 行出现错误:
ORA-02273: 此唯一/主键已被某些外键引用
SQL> alter table hr.worker drop constraint fk1;
表已更改。
SQL> alter table hr.deparment drop constraint PK2;
表已更改。
SQL> DROP TABLE hr.diy_os drop constraint pk0;
SQL> alter table hr.diy_os drop constraint PK0;
表已更改。
.......
.......
SQL> drop tablespace users including contents and datafiles;
表空间已删除。
同理做个小试验:
SQL> alter database default tablespace chao;
数据库已更改。
并在hr用户里创建了几张有主外键约束的表
SQL> drop tablespace chao including contents and datafiles;
drop tablespace chao including contents and datafiles
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
当然也可以禁用约束,不删除:
ALTER TABLE TABLE_NAME DISABLE CONSTRAINT CONSTRAINT_NAME;
启用约束:
ALTER TABLE TABLE_NAME ENABLE CONSTRAINT CONSTRAINT_NAME;
删除主键约束和相关的外键约束:
ALTER TABLE TABLE_NAME DROP PRIMARY KEY CASCADE;
ORA-02449, 00000, "unique/primary keys in table referenced by foreign keys"
// *Cause: An attempt was made to drop a table with unique or
// primary keys referenced by foreign keys in another table.
// *Action: Before performing the above operations the table, drop the
// foreign key constraints in other tables. You can see what
// constraints are referencing a table by issuing the following
// command:
// SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
我们删除一个表时,但是该表A的主键列或者唯一性列被引用为其他表B的外键,此时我们无法删除A
下面是以删除表空间users:
SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
SQL> alter table hr.employees drop CONSTRAINT emp_dept_fk;
表已更改。
通过下面的查询可以找出HR用户下所有表的主外键约束(已经确认只有hr用户使用users表空间)
select * from dba_constraints where constraint_type='F' AND OWNER='HR';
select * from dba_constraints where constraint_type='P' AND OWNER='HR';
下面删除了部分约束:
SQL> alter table hr.gongsi drop CONSTRAINT pk3;
表已更改。
SQL> alter table hr.worker drop constraint PK1;
表已更改。
SQL> alter table hr.deparment drop constraint PK2;
alter table hr.deparment drop constraint PK2
*
第 1 行出现错误:
ORA-02273: 此唯一/主键已被某些外键引用
SQL> alter table hr.worker drop constraint fk1;
表已更改。
SQL> alter table hr.deparment drop constraint PK2;
表已更改。
SQL> DROP TABLE hr.diy_os drop constraint pk0;
SQL> alter table hr.diy_os drop constraint PK0;
表已更改。
.......
.......
SQL> drop tablespace users including contents and datafiles;
表空间已删除。
同理做个小试验:
SQL> alter database default tablespace chao;
数据库已更改。
并在hr用户里创建了几张有主外键约束的表
SQL> drop tablespace chao including contents and datafiles;
drop tablespace chao including contents and datafiles
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
当然也可以禁用约束,不删除:
ALTER TABLE TABLE_NAME DISABLE CONSTRAINT CONSTRAINT_NAME;
启用约束:
ALTER TABLE TABLE_NAME ENABLE CONSTRAINT CONSTRAINT_NAME;
删除主键约束和相关的外键约束:
ALTER TABLE TABLE_NAME DROP PRIMARY KEY CASCADE;