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

问题描述

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

DROP TABLE SUBURB;掉落表库存；掉落表制造商；丢弃表仓库；滴表城市；删除表状态；

<块引用>

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

 创建表状态 (状态码 varchar(3),statename varchar(30),人口数(8),主键(状态码));

<块引用>

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

谁能解释为什么会这样?

解决方案

如果你真的确定要删除表，即使它在外键中被引用，你可以像这样强制它:

删除表状态级联约束；

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

请注意，这会删除任何外键关系.因此，您需要在重建表(及其主键)后重新创建它们.通常这没关系，因为最常见的用例是在开发或 CI 环境中丢弃和重新创建模式.

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

1. 在删除外键约束之前显式删除外键约束使用脚本或使用动态 SQL 的表.
2. 订购 DROPTABLE 语句，以便依赖表首先被 zapped用他们讨厌的外键.几张桌子很容易，大型架构更痛苦.

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;

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)
  );

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;

This syntax is defined in the Oracle SQL Reference.

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.

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

1. Explicitly drop the foreign key constraints before dropping the tables, either with a script or with dynamic SQL.
2. Order the DROP TABLE statements so that dependent tables are zapped first, along with their pesky foreign keys. Easy enough for a handful of tables, more painful with a large schema.

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