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

From: Mark A Singer <masinger_at_CORP.ROCKWELL.COM>
Date: Sat, 27 Jan 1996 18:18:43 -0500
Message-Id: <9601280025.AA04175@alice.jcc.com>

Tom's reply concerning my first comment (see below) is not valid for 7.1.3. If you try to create a foreign key that references a table that has a unique key but no primary key you will get the following message: "ora-02268: referenced table does not have a primary key". Thus, 7.1.3 requires a primary key to invoke referential integrity. Try it.

Also Tom's example, as written, will not work in 7.1.3.

Mark

----------------------[Reply - Original Message]----------------------

Sent by:"Coker, Tom - R&D" <tcoker_at_SCTCORP.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 Sat Jan 27 1996 - 19:25:31 CST