Oracle Database: Index-Organized Table with NULL Values (in Multiple-Column Primary Key)

4
votes

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! ♥

3
databaseoraclenullindexingprimary-key

3 Answers

2
votes

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.

1
votes

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

0
votes

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.