How to create a primary key using a longer unique index?

user_1864029 0
Viewed 345

One can specify the index while creating the primary key:

CREATE TABLE t (a NUMBER, b NUMBER);
ALTER TABLE t ADD PRIMARY KEY (a,b) USING INDEX (CREATE INDEX i ON t(a,b));

This works for column subsets, too:

ALTER TABLE t ADD PRIMARY KEY (a) USING INDEX (CREATE INDEX i ON t(a,b));

I prefer unique indexes (because a non-unique index adds the rowid to each key which makes the index bigger and slightly slower). This works, too:

ALTER TABLE t ADD PRIMARY KEY (a,b) USING INDEX (CREATE UNIQUE INDEX i ON t(a,b));

However, subset and unique index results in an error:

ALTER TABLE t ADD PRIMARY KEY (a) USING INDEX (CREATE UNIQUE INDEX u ON t(a,b));
ORA-14196: Specified index cannot be used to enforce the constraint.

To summarize:

OK     PRIMARY KEY (a,b) USING INDEX (        INDEX(a,b) )
OK     PRIMARY KEY (a,b) USING INDEX ( UNIQUE INDEX(a,b) )
OK     PRIMARY KEY (a)   USING INDEX (        INDEX(a,b) )
ERROR  PRIMARY KEY (a)   USING INDEX ( UNIQUE INDEX(a,b) )

I completely fail to understand why that is not possible.

I need it quite often, for instance for tables with two primary key columns (say country, city) and one further column (say population). As I always query the further column, a three column index would make sense. As the first two columns are unique (per primary key), the index will be unique as well, but Oracle won't let me do this. Why?

oracle sql
2 Answers

This is a comment that doesn't fit in the comments section, and it may be blatantly wrong.

My take is that Oracle can enforce the uniqueness of primary keys using either 1) a unique index or 2) a non-unique index (functioanality that may exist for historical reasons). Therefore:

  • Case #1: if it uses a unique index, all the heavy lifting of finding out if a value is unique is done by the index itself. It's part of its features.

  • Case #2: if it uses a non-unique index, the index is used to store the values, but the uniqueness is validated by the primary key constraint itself that scans the index for multiple values.

Now, your four examples fall into:

  • case #1 (non-unique)
  • case #2 (unique)
  • case #1 (non-unique)
  • not case #1, not case #2. This is why I think Oracle does not allow this one.

And of course, if anyone knows better, please correct me.

user_64361910

the unique index on the column (a,b) cannot be used to enforce a primary key on the column (a) and the database has rigthly prevented you doing this action.

This is because (1,100),(1,101),(1,102) are are legitimate values in the unique index on (a,b) where are enforcing that column a contains only 1,2,3,...etc cannot be actioned using the same index.

user_71370900
Related