没有索引的唯一约束 [英] Unique constraint without index

问题描述

假设我有一张大桌子.

此表无需查询，我只想在其中保存一段时间.

This table not need to be queried, I just want to save the data inside for a while.

我想防止表中的行重复，所以我想添加一个唯一的

I want to prevent duplicates rows in the table, so I want to add an unique

表上的约束(或PK).

constraint (or PK) on the table.

但是自动创建的唯一索引确实是不必要的.

But the auto-created unique index is realy unnecessary.

我不需要它，这只是在浪费磁盘空间，需要维护 (无论创建时间长).

I don't need it, and it's just wasting space in disk and require a maintenance (regardless of the long time to create it).

有没有一种方法可以创建不带索引的唯一约束(任何索引-唯一或不唯一)?

Is there a way to create an unique constraint without index (any index - unique or nonunique)?

谢谢.

推荐答案

否，如果没有相应的索引，您将无法在Oracle中拥有UNIQUE约束.添加约束后，索引会自动创建，任何删除索引的尝试都会导致错误

No, you can't have a UNIQUE constraint in Oracle without a corresponding index. The index is created automatically when the constraint is added, and any attempt to drop the index results in the error

ORA-02429: cannot drop index used for enforcement of unique/primary key

好运.

但是您说假设我有一张大桌子".那么我们在这里讨论多少行?看吧，1TB SSD不到100美元.四核笔记本电脑的价格不到400美元.如果您想通过编写一堆代码来最大程度地减少存储"或节省时间"的适用性，以最大程度地减少存储使用或CPU的消耗，我的建议是您在浪费两者 时间和金钱.我再说一遍-一张TERABYTE的存储成本与编程时间的一小时相同.品牌推广新计算机的成本与程序员花费了四千小时的时间相同.您要尽一切可能使编码时间最小化，而不是传统的CPU时间或磁盘空间优化目标，要好得多.因此，我认为UNIQUE索引是一种低成本解决方案.

But you say "Let's say I have a large table". So how many rows are we talking about here? Look, 1TB SSD's are under $100. Quad-core laptops are under $400. If you're trying to minimize storage use or CPU burn by writing a bunch of code with minimal applicability to "save money" or "save time" my suggestion is that you're wasting both time and money. I repeat - ONE TERABYTE of storage costs the same as ONE HOUR of programmer time. A BRAND SPANKING NEW COMPUTER costs the same as FOUR LOUSY HOURS of programmer time. You are far, far better off doing whatever you can to minimize CODING TIME, rather than the traditional optimization targets of CPU time or disk space. Thus, I submit that the UNIQUE index is the low cost solution.

这篇关于没有索引的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！