Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do you create a double-primary key?
<though not always true> I believe it is poor practice to use composite
keys, choosing a UNIQUE constraint
(via a unique index) might be a better choice.
Although I have lots of situations where I violate this, the textbooks suggest that the primary key should not contain "data", particularly when the data (or any component of the composite key) might change.
Also, I really try to stay away from composite indexes. When accessing a composite index when one or more of the composite fields is missing requires a FULL-INDEX scan (not to be confused with a full-table scan). For large indexes, this is not very efficient.
"News" <francesco.marchioni_at_intra.lastminutetour.com> wrote in message
news:8q9q18$co3$1_at_marte.lastminutetour.com...
> Thanks.
> Very clear and very effective.
> Bye
> Francesco
>
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:39c7f83f_at_news.iprimus.com.au...
> > What you're saying is that the combination of ID1 and ID2 is the primary
key
> > on this table. That's a concatentated primary key, and is easily done
with
> > the alter table command
> >
> > Alter table xxxx
> > Add (Constraint <name> Primary Key (ID1,ID2) )
> > deferrable initially immediate
> > Using Index
> > Tablespace indx01
> >
> > Key things: ignore the 'deferrable' line if you don't have Oracle 8 or
> > above.
> > Always house the index that will be automatically created for you when
you
> > declare a primary key in the appropriate index tablespace.
> >
> > Regards
> > HJR
> > --
>
> --------------------------------------------------------------------------
> > Opinions expressed are my own, and not those of Oracle Corporation
> > Oracle DBA Resources:
http://www.geocities.com/howardjr2000
>
> --------------------------------------------------------------------------
> >
> >
> >
> > "Francesco Marchioni" <Francesco.Marchioni_at_intra.lastminutetour.com>
wrote
> > in message news:8q7ruq$g9o$1_at_marte.lastminutetour.com...
> > > Hello,
> > > I'd like to know how can I create a table with a double primary key?
> > > I mean I have two keys
> > >
> > > ID1 ID2
> > >
> > > and it's legal to have duplicates of ID1 or duplicates of ID2 but not
of
ID1
> > > and ID2 together.
> > > Example:
> > >
> > > ID1 ID2
> > > 1 5 Ok
> > > 1 4 Ok
> > > 3 4 Ok
> > > 1 5 Wrong. I don't want to allow this!
> > >
> > > Does anybody know how to do it?
> > > Thanks
> > > Francesco
> > >
> > >
> >
> >
>
>
Received on Wed Sep 20 2000 - 00:00:00 CDT