Comments below
HJR
--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------
"Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote in message
news:8qasof$nl7$1_at_spiney.sierra.com...
> <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.
>
Two things. First a Unique constraint still ends up (as you point out)
creating a concatenated index, so I'm not sure what you've saved or gained
by doing this. On the other hand, a Unique constraint would allow nulls, so
I can see precisely what you've *lost* by doing this. Second, why use a
unique index for such a constraint? If you have the choice, use a
non-unique index to enforce the uniqueness of the constraint. That way, it
doesn't disappear on you automatically when you choose to temporarily
disable the constraint -removal of the index is then down to you performing
a manual drop if the circumstances warrant it. I prefer to be in control of
things like entire segments going walkabout on me!
> 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.
>
Mmm. I sort of take the point... but I'd go on to say that if bits of my
primary key are subject to change, it can't *really* be the primary key!
Steve Adams tends to recommend synthetic keys in (nearly) all cases, and
I've seen that advice doing the rounds for years. Never been happy with it
myself, since the use of some form of autoincrementing number will always be
unique -even if you then go on to insert duplicating data. Synthetic keys
might be good for access performance and so on, but I like things which
ought to be unique to actually be *enforced* as unique.
> 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.
>
As I said before, you've already pointed out that if the original poster
wants the combination of his three fields to be unique, he's not going to be
able to avoid a concatenated index.
> "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 Thu Sep 21 2000 - 00:00:00 CDT
