Oracle数据库：具有NULL值的索引组织表（在多列主键中）

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...

当该列是多列主键的一部分时,如何将列设置为“”(空字符串,相当于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 ...

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...

通常情况下,我会使用“真正的”主键,如无意义的顺序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 ...

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?

但是,这是一个很大的索引组织表(IOT),所以主键必须在数据列上(在IOT中,数据是索引)或者......我该怎么办?

Thanks! ♥

3 个解决方案

#1

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.

不幸的是,这是Oracle特有的“功能”。与其他SQL DBMS不同,它不支持零长度字符串值,并坚持将其转换为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.

您可以创建另一列作为标志来表示零长度字符串,然后存储空格来代替列本身。我不认为有一个完美的解决方法。有点令人惊讶的是Oracle没有解决这个奇怪的限制 - 特别是考虑到他们在其他方面遵循SQL标准所做的巨大改进。

#2

replace the empty string with a dummy string and check for it later... depends how the table is bieng used though.

用虚拟字符串替换空字符串并稍后检查...取决于表格是如何使用的。

#3

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.

为什么你需要桌子作为物联网?对于具有堆表的column1,column2上的UNIQUE INDEX,可能几乎同样有效地进行数据检索。

IOTs (similar to a table with a clustered index) are the exception, not the norm in Oracle.

IOT(类似于具有聚簇索引的表)是例外,而不是Oracle中的标准。

#1