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

--Boundary-8143026-0-0

Well, it seems to work ok on version 7.1.3 on my HP-UX machine...

Tim Sawmiller
tsawmiller_at_us.oracle.com
"The opinions expressed here are my own and not necessarily those of the Oracle Corporation".

--Boundary-8143026-0-0
Content-Type: message/rfc822

Date: 30 Jan 96 14:05:52
From:"Coker, Tom - R&D" <tcoker_at_SCTCORP.COM> To: Multiple,recipients,of,list,ORACLE-L,ORACLE-L_at_CCVM.SUNYSB.EDU Subject: Re: Diff. betwn Primary key and Unique Key with Exs. Reply-to: ORACLE-L_at_CCVM.SUNYSB.EDU
X-Orcl-Application: Sender: "ORACLE database mailing list."  <ORACLE-L_at_CCVM.SUNYSB.EDU>
X-To: "ORACLE database mailing list." <ORACLE-L_at_CCVM.SUNYSB.EDU> X-Orcl-Application: In-Reply-To: <199601280035.TAA17025_at_sctsun9.sctcorp.com>

Must be a 7.1.3 thing. It works in 7.1.4. How about later releases?

tcoker

<<<On Fri, 26 Jan 96 18:14:55 EST , Mark A Singer wrote>>>

>
> 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>>>>>
>
> =====================================================================
>

<<<End of Message from Mark A Singer>>>

--Boundary-8143026-0-0-- Received on Wed Jan 31 1996 - 14:57:59 CST