oracle 唯一/主键的强制执行 - 删除索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17648681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Enforcement of unique/primary key - drop index
提问by Moudiz
I am trying to drop an index :
我正在尝试删除索引：
DROP INDEX PK_CHARGES
but I get this error
但我收到这个错误
cannot drop index used for enforcement of unique/primary key
无法删除用于强制执行唯一键/主键的索引
Why I am getting this error? I will provide further information if you need any.
为什么我收到这个错误？如果您需要任何信息，我会提供更多信息。
How to solve it?
如何解决？
EditI have no primary key in the table, but I found this weird index that I don't remember I had added:
编辑我在表中没有主键，但我发现这个奇怪的索引，我不记得我添加过：
index name = SYS_C0040476 which have the same columns
索引名称 = SYS_C0040476 具有相同的列
回答by Alex Poole
You can query the ALL_CONSTRAINTSperformance viewto see which constraint the index is used by, and which table it applies to, e.g:
您可以查询ALL_CONSTRAINTS性能视图以查看索引使用的约束以及它适用于哪个表，例如：
select owner, constraint_name, constraint_type,
table_name, index_owner, index_name
from all_constraints
where index_name = 'PK_CHARGES';
I would expect the table name to be 'CHARGES', the constraint name to match the index name, and the constraint type to be 'P'. But since you have a table in mind, perhaps the names aren't following a helpful convention. Maybe an old version of the table was renamed, which would leave the constraints against the new name (e.g. CHARGES_BACKUPor something).
我希望表名是'CHARGES'，约束名与索引名相匹配，约束类型是'P'。但是，由于您有一个表格，因此名称可能没有遵循有用的约定。也许旧版本的表被重命名，这会留下对新名称的约束（例如CHARGES_BACKUP或其他东西）。
You said you click on the table, then on the view. Perhaps you're not looking at the table that the constraint/index is on; or perhaps you're looking at a view on top of the actual table. You also mention a SYS_index on the same columns - which can't be on the same table. Do you have multiple similar tables, or access to multiple schemas? You shold run the above query for that index too. As mentions above, you might find an old version (or versions) of the table.
你说你点击表格，然后点击视图。也许您没有查看约束/索引所在的表；或者您可能正在查看实际表顶部的视图。您还提到了SYS_同一列上的索引 - 不能在同一个表上。您是否有多个相似的表，或访问多个模式？您也应该为该索引运行上述查询。如上所述，您可能会发现该表的旧版本（或多个版本）。
Once you've identified which table the constraint is on, you'll need to decide whether you should actually be keeping it, and if not you can remove it by dropping the constraint with an ALTER TABLEcommand.
一旦确定了约束在哪个表上，您需要决定是否应该真正保留它，如果不是，则可以通过使用ALTER TABLE命令删除约束来删除它。
回答by Eduardo Yá?ez Parareda
The problem with
问题在于
But I found this weird index that I dont rember I hadve add
但是我发现了这个奇怪的索引，我不记得我已经添加了
comes because you didn't add it. You had a primary key, then you dropped it, but when you do that Oracle doesn't drop the associated unique index that every primary key has.
来是因为你没有添加它。您有一个主键，然后您删除了它，但是当您这样做时，Oracle 不会删除每个主键具有的关联唯一索引。
So when you drop a primary key you have todrop the unique index of that primary key, that amazingly has the same name as the primary key had.
因此，当您删除一个主键时，您必须删除该主键的唯一索引，该索引与主键的名称惊人地相同。
So for dropping a MY_TABLE_PK you must do:
因此，要删除 MY_TABLE_PK，您必须执行以下操作：
ALTER TABLE MY_TABLE DROP PRIMARY KEY DROP INDEX;
so you ensure that the index is dropped as well.
所以你确保索引也被删除。
回答by APC
"from pl/sql I right click on the table"
“从 pl/sql 我右键单击表”
The problem with IDEs is that they make us feel very productive, because we can do things with just a click instead of writing some code. This is a problem because when something unusual happens the IDE is no good for investigation and we lack the understanding of the underlying structure of the database which we need to help ourselves.
IDE 的问题在于它们让我们感觉非常高效，因为我们只需单击一下就可以完成任务，而无需编写一些代码。这是一个问题，因为当发生异常情况时，IDE 不利于调查，我们缺乏对数据库底层结构的了解，我们需要帮助自己。
If you want to learn the Oracle database the worst thing you can do is download SQL Developer or PLSQL Developer or TOAD. They're all fine tools but the only people who should use them are the people who don't need to use them.
如果您想学习 Oracle 数据库，最糟糕的事情就是下载 SQL Developer 或 PLSQL Developer 或 TOAD。它们都是很好的工具，但唯一应该使用它们的人是不需要使用它们的人。
回答by bambrikii
the following worked for me with unique index:
以下对我有用的唯一索引：
ALTER INDEX UX_CHARGES UNUSABLE
/
DROP INDEX UX_CHARGES
/
see: https://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes004.htm#insertedID3
见：https: //docs.oracle.com/cd/E18283_01/server.112/e17120/indexes004.htm#insertedID3
