Re: Primary key question

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 12 Feb 2008 13:29:34 -0800 (PST)
Message-ID: <1f827ded-9f2b-44ef-9879-dd7cc5b67542@i7g2000prf.googlegroups.com>

On Feb 12, 3:30 pm, spamb..._at_milmac.com (Doug Miller) wrote:
> Background: I have 5+ years experience in SQL on Tandem systems, but I'm an
> Oracle newbie trying to adjust to the differences in the two implementations.
>
> This would work in Tandem SQL/MP:
>
> create table abc (a char(8), b char(8), c char(8),
>    primary key (a, b, c descending));
>
> but it doesn't seem to work in SQL*Plus 10.1.0.4.2 -- do I have the syntax
> wrong somehow, or does Oracle simply not support descending sequence in a
> primary key column?

At least on Oracle, char(8) columns are always 8 characters, padded with spaces if necessary. VARCHAR2 is commonly used to avoid this potential issue.

Let's try an experiment:
Create a basic table without a primary key: CREATE TABLE ABC(

  A VARCHAR2(8),
  B VARCHAR2(8),
  C VARCHAR2(8));

Create an index that *might* be used to assist in the enforcement of the primary key constraint:
CREATE UNIQUE INDEX ABC_IND ON ABC(A,B,C DESC); Now, let's add the primary key constraint: ALTER TABLE ABC ADD (PRIMARY KEY (A,B,C)); Then, check the indexes on the table:
SELECT
  INDEX_NAME,
  INDEX_TYPE
FROM
  USER_INDEXES
WHERE
  TABLE_NAME='ABC'; INDEX_NAME INDEX_TYPE

---------------- ---------------------
ABC_IND          FUNCTION-BASED NORMAL
SYS_C0024279     NORMAL

The ABC_IND index is a function based index (caused by the DESC clause), not a normal index. Oracle created a system generated index to assist in the enforcement of the primary key constraint.

Test #2:
DROP TABLE ABC; CREATE TABLE ABC(

  A VARCHAR2(8),
  B VARCHAR2(8),
  C VARCHAR2(8));

CREATE UNIQUE INDEX ABC_IND ON ABC(A,B,C); ALTER TABLE ABC ADD (PRIMARY KEY (A,B,C)); SELECT
  INDEX_NAME,
  INDEX_TYPE
FROM
  USER_INDEXES
WHERE
  TABLE_NAME='ABC'; INDEX_NAME INDEX_TYPE

---------------- ----------
ABC_IND          NORMAL

Only 1 index listed this time.

Test #3:
DROP TABLE ABC; CREATE TABLE ABC(

  A VARCHAR2(8),
  B VARCHAR2(8),
  C VARCHAR2(8),

  PRIMARY KEY (A,B,C DESC)); ERROR at line 5:
ORA-00907: missing right parenthesis

Test #4:
CREATE TABLE ABC(

  A VARCHAR2(8),
  B VARCHAR2(8),
  C VARCHAR2(8),

  PRIMARY KEY (A,B,C)); SELECT
  INDEX_NAME,
  INDEX_TYPE
FROM
  USER_INDEXES
WHERE
  TABLE_NAME='ABC'; INDEX_NAME INDEX_TYPE

---------------- ----------
SYS_C0024281     NORMAL

Test #5:
DROP TABLE ABC; CREATE TABLE ABC(

  A VARCHAR2(8),
  B VARCHAR2(8),
  C VARCHAR2(8));

CREATE UNIQUE INDEX ABC_IND ON ABC(A,B,C DESC); ALTER TABLE ABC ADD (
  PRIMARY KEY (A,B,C DESC)); ERROR at line 2:
ORA-00907: missing right parenthesis

Looks like Oracle does not like to use function based indexes to assist in the enforement of primary key constraints, or more likely, the primary key cannot include DESC to determine the sort order of the primary key index.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Feb 12 2008 - 15:29:34 CST