Re: Diff. betwn Primary key and Unique Key with Exs.

From: Coker, Tom - R&D <tcoker_at_SCTCORP.COM>
Date: Fri, 26 Jan 1996 10:00:14 EST
Message-Id: <9601261745.AA04983@alice.jcc.com>

Reply embedded in message.....

<<<<<On Thu, 25 Jan 96 15:24:01 EST , Mark A Singer wrote:>>>>>
>
> There are other differences:
>
> 1. primary keys must be used for referential integrity; unique is not
> used in referential integrity.
>

This is not true. You can use unique keys in referential integrity (Server Concepts Manual pg. 7-11). The following works:

create table tab1
( tab1_col1 varchar2(10) not null,
  tab1_col2 varchar2(10) ,
  constraint uk1_tab1 (tab1_col1,tab1_col2) );

create table tab2

( tab2_col3 varchar2(10) not null,
  tab2_col1 varchar2(10) not null,
  tab2_col2 varchar2(10)         ,

  constraint pk_tab2 (tab2_col3)
);

alter table tab2
add constraint fk1_tab2_refs_tab1
foreign key (tab2_col1,tab2_col2)
references scott.tab1 (tab1_col1,tab1_col2);

> 2. you can only have 1 primary key per table; you can have more than 1
> unique key per table (i.e., employee_number & ssn).

Agreed, good point.

tcoker

> ----------------------[Reply - Original Message]----------------------
>
> Sent by:"Coker, Tom - R&D" <tcoker_at_SCTCORP.COM>
> Rajesh,
>
> Simply put, the only difference between the two is the ability to
> have null values within a UNIQUE key. PRIMARY keys disallow
> duplicates AND nulls where UNIQUE keys only disallow duplicates.
>
> tcoker
>
> <<<<<On Thu, 25 Jan 96 23:08:25 EST , Rajesh.S.Bhave wrote:>>>>>
>
> >
> > Hi Everyone,
> >
> > I am new to ORACLE. Please mail me differences between Primary key
> > and Unique
> > Key. This should preferably be with examples, corresponding to
> > foreign key.
> > For one example, parent key should be PRIMARY and for the other it
> > should be
> > UNIQUE.
> >
> > Thanks and Regards,
> > Rajesh
> >
> > e-mail : rajeshb_at_vishnu.india.hp.com
> >
> ====================================================================
> > ============
> >
>
> <<<<<End of reply to Rajesh.S.Bhave>>>>>
>

<<<<<End of reply to Mark A Singer>>>>> Received on Fri Jan 26 1996 - 12:45:27 CST