Re: Primary Key vs Unique Key

"Chapman Keyes" <chapman_at_teoco.com> wrote in message news:a80c63.0105211038.323261bb_at_posting.google.com...
> Greetings!
> Please help me understand the practical differences on the db
> side between creating indices as Primary Keys vs. Unique Keys on the
> same columns. The reason I ask is that when we create tables with the
> PK embedded, Oracle assigns a SYS_Cnnnnn index name, and those names
> differ from db to db, making direct changes to the PK's at our
> customer sites impossible.

So, welcome to the reason for learning the PRIMARY KEY constraint syntax properly. You know, I hope, the logical difference between uniques and primaries -a primary key IS a unique constraint, PLUS a not null. Uniques on their own can contain nulls.

Your problem is simply that you are not naming your constraints when you declare them. The short syntax goes something like this:

Create table blah(
col1 number CONSTRAINT name_of_constraint PRIMARY KEY .....etc etc etc

...When you specify either a unique or a primary key constraint, Oracle will generally create a new index for you on that column (the exception is if you have already created your own index on the same column...it will then re-use that existing index rather than create a new one). The name it gives to that new index is the same name as you give to your constraint. If you don't name your constraint, it assigns it one of the SYS... numbers that are, I agree, totally unhelpful and impractical.

So! Name your constraints properly, and all should be well.

I'm hoping you also realise that the index created by the above syntax would ordinarily end up being created in the User's default tablespace? And that you ought instead to properly house the index is a more appropriate tablespace? The extended syntax runs as follows:

Create table blah(
col1 number CONSTRAINT name_of_constraint PRIMARY KEY USING INDEX TABLESPACE IDX1 ..... etc etc etc

That's true of Uniques, as well.

And you ought to consider the issues of DEFERRABLE INITIALLY IMMEDIATE (or initially deferred -whatever, the thing is that your Primaries and Uniques ought to be declared DEFERRABLE). If one of these constraints is declared deferrable, thenthe index that Oracle will create for you to enforce the constraint will actually be a non-unique index. That doesn't affect Oracle's ability to enforce uniqueness, but it does mean that when you disable the *constraint*, the index used to enforce it is NOT dropped. If the constraint is enforced with a unqieu index, it *is* dropped -automatically, and without your say-so. Which means that you can find yourself expecting to do a quick disable, insert, re-enable, and suddenly find your table is locked for hours whilst the entire index is being rebuilt from scratch.

Personally, I like to decide for myself when indexes should disappear, so all my unique and primary key constraints are declared deferrable, so I get that choice.

Regards
HJR
> We do not have R.I., but might in the future. All our PK's are
> sequence ID's. I understand the value of PK's in data modeling, but
> ERwin allows us the option of creating the tables in ERwin with the
> PK's (which allows click-and-click FK creation) and then generating
> the SQL either with the PK embedded in the CREATE TABLE, or with
> separate CREATE UNIQUE INDEX XPKtablename scripts that allow us to
> maintain identical index names across all our db's.
> So, does the db (Oracle) itself care whether a column is indexed
> as PK or UK? If so, under what conditions? Thanks!
Received on Mon May 21 2001 - 16:50:17 CDT