Re: Primary key question
- From: spambait@xxxxxxxxxx (Doug Miller)
- Date: Wed, 13 Feb 2008 15:04:48 GMT
In article <1f827ded-9f2b-44ef-9879-dd7cc5b67542@xxxxxxxxxxxxxxxxxxxxxxxxxxx>, Charles Hooper <hooperc2000@xxxxxxxxx> wrote:
On Feb 12, 3:30=A0pm, spamb...@xxxxxxxxxx (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.
.
- References:
- Primary key question
- From: Doug Miller
- Re: Primary key question
- From: Charles Hooper
- Primary key question
- Prev by Date: Re: query....
- Next by Date: Re: Primary key question
- Previous by thread: Re: Primary key question
- Next by thread: Re: Primary key question
- Index(es):
Relevant Pages
|
