Re: Primary Key vs Unique Key

From: Van Messner <vmessner_at_bestweb.net>
Date: Mon, 21 May 2001 21:52:23 GMT
Message-ID: <r6gO6.2435$gA.951658@monger.newsread.com>

Here's another syntax you can use as part of each table creation - names the primary key and places/sizes the index and table

Van:

Create Table LDCOMMON.ADDRESSES_MSTR (

ADDRESS_KEY              NUMBER(8)  NOT NULL,
ADDRESS_TYPE_NAME      VARCHAR2(40) NOT NULL,     --fk1
ADDRESS_STREET_LINE1  VARCHAR2(50) NULL,
ADDRESS_STREET_LINE2  VARCHAR2(50) NULL,
ADDRESS_STREET_LINE3  VARCHAR2(50) NULL,
LOCATION_KEY   NUMBER(8) NOT NULL,                     --fk2
ADDRESS_POSTAL_CODE  VARCHAR2(20) NULL,

ADDRESS_DIRECTIONS VARCHAR2(1000) NULL, ADDRESS_NOW_ACTIVE VARCHAR2(5) DEFAULT 'YES' NOT NULL CHECK (ADDRESS_NOW_ACTIVE IN ('YES', 'NO')),

ADDRESS_DATE_MADE_INACTIVE DATE  NULL,
ADDRESS_CREATE_DATE  DATE  NULL,
ADDRESS_CREATE_PERS  VARCHAR2(40) NULL,
ADDRESS_CREATE_WEBPAGE  VARCHAR2(200) NULL,
ADDRESS_LAST_MODIFY_DATE DATE  NULL,
ADDRESS_LAST_MODIFY_PERS VARCHAR2(40) NULL,
ADDRESS_LAST_MODIFY_WEBPAGE VARCHAR2(200) NULL,
ADDRESS_DESC   VARCHAR2(200) NULL,

  constraint ADDRESS_MSTR_PK primary key (ADDRESS_KEY)   deferrable initially immediate
  using index tablespace COMMON_MEDIUM
  storage (initial 256K next 256K pctincrease 0 minextents 1 maxextents 249) )
tablespace COMMON_LARGE
storage (initial 2M next 2M pctincrease 0 minextents 1 maxextents 249) ;

"Lee Miller" <lpm_at_newsguy.com> wrote in message news:9ebstk02gfb_at_enews1.newsguy.com...
> 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 - 16:52:23 CDT