Re: Primary key question

In article <1f827ded-9f2b-44ef-9879-dd7cc5b67542_at_i7g2000prf.googlegroups.com>, Charles Hooper <hooperc2000_at_yahoo.com> wrote:
>On Feb 12, 3:30=A0pm, 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.

Yeah, same on the Tandem -- just laziness on my part in constructing the example.
>
>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=3D'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.

Right, already discovered I could do that, but it's not quite what I was aiming for.
>
>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=3D'ABC';
>
>INDEX_NAME INDEX_TYPE
>---------------- ----------
>ABC_IND NORMAL
>
>Only 1 index listed this time.

Yes, but the sequence is ascending on all three columns. This specific application needs the third column to be in descending sequence.
>
>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

Yep, same problem I ran into.
>
>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=3D'ABC';
>
>INDEX_NAME INDEX_TYPE
>---------------- ----------
>SYS_C0024281 NORMAL
Again, while this works, it's necessary to have the third column in descending sequence.
>
>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

Yep, same thing I encountered.
>
>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.

Thanks for the detailed reply, Charles. Looks like the way to go is the one shown in your first example, with a unique non-primary index. Received on Wed Feb 13 2008 - 09:04:48 CST