在 Oracle SQL 中删除表

本教程将介绍在 Oracle SQL 中删除表的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

在 Oracle SQL 中删除表 教程 第1张

问题描述

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

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

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

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

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

谁能解释为什么会这样?

解决方案

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

删除表状态级联约束;

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

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

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

    在删除外键约束之前显式删除外键约束使用脚本或使用动态 SQL 的表.

    订购 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:

    Explicitly drop the foreign key constraints before dropping the
    tables, either with a script or with dynamic SQL.

    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 中删除表的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。