Oracle 数据库：具有 NULL 值的索引组织表（多列主键）
当该列是多列主键的一部分时，如何将该列设置为“”（空字符串，相当于 Oracle 中的 NULL）？这就是动机......
CREATE TABLE entities (
column1 VARCHAR2(10)
, column2 VARCHAR2(10)
, body VARCHAR2(4000)
, CONSTRAINT pk_entities -- can't do this, because sometimes
PRIMARY KEY ( column1, column2 ) -- col2 is the empty string (NULL).
) ORGANIZATION INDEX ...
通常情况下，我会使用“真正的”主键，例如无意义的顺序ID（请参阅这个问题），然后对我的数据列施加唯一约束，就像这样......
CREATE TABLE entities (
, id NUMBER PRIMARY KEY
, column1 VARCHAR2(10)
, column2 VARCHAR2(10)
, body VARCHAR2(4000)
, CONSTRAINT unq_entities
UNIQUE ( column1, column2 )
) ORGANIZATION INDEX ...
但是，这是一个很大的索引组织表（IOT），所以主键必须位于数据列上（在IOT中，数据是索引），否则......我该怎么办？
谢谢！ ♥
How can I set a column to be an "" (the empty string, equivalent to NULL in Oracle), when that column is part of a multiple-column primary key? This is the motivation...
CREATE TABLE entities (
column1 VARCHAR2(10)
, column2 VARCHAR2(10)
, body VARCHAR2(4000)
, CONSTRAINT pk_entities -- can't do this, because sometimes
PRIMARY KEY ( column1, column2 ) -- col2 is the empty string (NULL).
) ORGANIZATION INDEX ...
Normally, I'd use a "real" primary key like a meaningless sequential id (see this question) and then put a unique constraint over my data columns, like so...
CREATE TABLE entities (
, id NUMBER PRIMARY KEY
, column1 VARCHAR2(10)
, column2 VARCHAR2(10)
, body VARCHAR2(4000)
, CONSTRAINT unq_entities
UNIQUE ( column1, column2 )
) ORGANIZATION INDEX ...
However, this is a big index-organized table (IOT), so the primary key has to be on the data columns (in IOTs, the data is the index) or else... What should I do?
Thanks! ♥
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不幸的是，这是 Oracle 特有的“功能”。与其他 SQL DBMS 不同，它不支持零长度字符串值，并坚持将其转换为 null。
您可以创建另一列作为标志来表示零长度字符串，然后存储空格来代替列本身。但我认为没有完美的解决方法。令人有点惊讶的是，Oracle 没有解决这个奇怪的限制 - 特别是考虑到他们在其他方面为遵守 SQL 标准所做的巨大改进。
Unfortunately this is a "feature" peculiar to Oracle. Unlike other SQL DBMSs it doesn't support the zero-length string value and insists on converting it to a null.
You could create another column as a flag to represent the zero-length string and then store spaces in place of the column itself. I don't think there is a perfect workaround though. It's a little surprising that Oracle hasn't fixed this odd limitation - especially given the enormous improvements they've made in other ways to comply with the SQL standard.
将空字符串替换为虚拟字符串并稍后检查...但这取决于表的使用方式。
replace the empty string with a dummy string and check for it later... depends how the table is bieng used though.
为什么您需要将桌子打造成物联网？具有堆表的列 1、列 2 上的 UNIQUE INDEX 对于数据检索来说可能几乎同样有效。
IOT（类似于具有聚集索引的表）是例外，而不是 Oracle 中的常态。
Why do you need the table to be an IOT? a UNIQUE INDEX on column1,column2 with a heap table will probably be nearly as efficient for data retrieval.
IOTs (similar to a table with a clustered index) are the exception, not the norm in Oracle.