Re: Primary key question

On Feb 12, 3:29 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

SQL> create table abc(

  2  a varchar2(8),
  3  b varchar2(8),
  4  c varchar2(8));

Table created.

SQL> create index abc_ind
  2 on abc(a, b, c desc)
  3 /

Index created.

SQL> alter table abc
  2 add constraint abc_pk
  3 primary key(a,b,c)
  4 using index abc_ind;
alter table abc
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

SQL> David Fitzjarrell Received on Wed Feb 13 2008 - 08:22:37 CST