Re: Primary Key vs Unique Key

Primary key automatically carries not null constraint, unique index on same column does not. Other than that a not null column with a unique index cannot be the target of a foreign key constraint (At least as of Oracle 7.x it didn't), so if you do have RI in the future you would need PK's not UK's. If you want to have reproducable names I'd suggest creating your table without an index and then doing an:

ALTER TABLE tablename ADD CONSRTAINT pk_constraint_name PRIMARY KEY ( column )

we do this with all our constraints (pk, nn, column checks, etc) and it makes it much easier to find them if you ever need to disable them.

"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.
> 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 - 15:14:51 CDT