在Oracle SQL中删除表 [英] Dropping a table in Oracle SQL

问题描述

每当我尝试删除表或创建表时，都会显示以下错误:

Whenever I try to drop a table or create a table it is showing these errors:

DROP TABLE SUBURB;
DROP TABLE STOCKITEM;
DROP TABLE MANUFACTURER;
DROP TABLE WAREHOUSE;
DROP TABLE CITY;
DROP TABLE STATE;

第1行错误:ORA-02449:表中引用的唯一/主键通过外键

Error at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys

 CREATE TABLE STATE (
 statecode varchar(3)
 ,statename varchar(30)
 ,population number(8)
 ,primary key(statecode)
  );

第1行出现错误:ORA-00955:现有对象已经使用了名称

Error at line 1: ORA-00955: name is already used by an existing object

有人可以解释为什么会这样吗?

Can anybody explain why this happens?

推荐答案

如果您确实确定要删除该表，即使该表已在外键中引用，也可以这样强制它:

If you're really sure you want to drop the table even though it's referenced in foreign keys you can force it like this:

drop table state cascade constraints;

此语法在《 Oracle SQL参考》中定义.

请注意，这会删除所有外键关系.因此，在重建表(及其主键)之后，您将需要重新创建它们.通常这是可以的，因为最常见的用例是在Development或CI环境中破坏和重新创建架构.

Note that this drops any foreign key relationships. So you will need to recreate them after you have rebuilt the table (and its primary key). Normally this is okay because the most common use case is trashing and re-creating schemas in Development or CI environments.

我们可以使用 cascade约束使我们的构建脚本更易于维护.有两种选择:

We can use cascade constraints to make our build scripts easier to maintain. There are two alternatives:

1. 明确删除外键约束，然后再删除表，可以使用脚本或动态SQL.
2. 订购DROPTABLE语句，以便首先转换从属表与他们讨厌的外键.简单易用，可容纳几张桌子，使用大型架构会更加痛苦.

这篇关于在Oracle SQL中删除表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！