ORA-02449
来源:互联网 发布:mysql数据库租用 编辑:程序博客网 时间:2024/06/03 22:04
关于这个错误:
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;
0 0
- ORA-02449
- ORA-02449
- ORA-02449 的问题
- ORA-02449 的问题
- ORA
- ora
- oracle在操作主、外键需要注意的ORA-02449、ORA-02298
- ORA-01034 ORA-27101
- ORA-10567,ORA-00313
- ora-01128,ora-00275
- ORA-03113,ORA-07445
- ORA-32017 ORA-16179
- ORA-12012,ORA-12005
- ora-01034 ,ora-27101
- ora-603 ora-27504
- ora-01113 ora-01110
- ORA-24324 & ORA-01041
- tnsnames.ora & listener.ora
- 带阅读
- TinyXML4
- 文档
- WebSphere--连接管理器
- POJ - 2192 - Zipper (简单DP)
- ORA-02449
- 资料
- #job 26
- web前端环境搭建细解【dreamweaver+jdk+tomcat(服务器)】
- 待阅读
- 资料
- WebSphere--用户简要表
- php中各种输出字符串的区别echo、print、print_r和printf 的区别 (转)
- 是月年的我