I think there are a number of ways to implement something like that (like
catching exceptions), and choosing solutions like permitting temporary
states of non-uniqueness is asking for trouble. As long as I can see any
other option to return the same result, I would take it. This also prevents
the developers from getting lazy and delivering 'quick-and-dirty' solutions
:).
Enabling a primary key by deferred constraint checking is something that I
would consider, because in that case it is still Oracle that enforces the
constraint, and at the and of the transaction, no duplicate values are
allowed, but I didn't think this was the issue here. I thought it was about
disabling constraints, fooling around and then enabling the constraints with
the "novalidate" option.
hth too
Remco
-----Oorspronkelijk bericht-----
Van: Connor McDonald [mailto:hamcdc_at_yahoo.co.uk]
Verzonden: dinsdag 12 juni 2001 14:31
Aan: Multiple recipients of list ORACLE-L
Onderwerp: RE: Enforced Costraints ??
I disagree. A simple reason for a non-unique index to
enforce a primary key are is to allowing a temporary
state of "non-uniqueness" to be permitted during a
transaction. eg insert a batch of new records (some
of which may be duplicates), then remove the bad ones,
then commit. (with deferred constraint checking)
hth
connor
- "Daemen, Remco" <R.Daemen_at_facent.nl> wrote: > Not
good enough ! According to various documents,
> you should add your own
> primary key (surrogate key), which also makes it
> easier to keep track of
> history and combine various sources. I agree with
> Lee: you should never
> implement a PK with non-unique values.
>
> Remco
>
> -----Oorspronkelijk bericht-----
> Van: Rahul [mailto:rahul_at_ratelindo.co.id]
> Verzonden: dinsdag 12 juni 2001 11:56
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: RE: Enforced Costraints ??
>
>
> DSS !
>
>
> > ----------
> > From: Robertson Lee -
> lerobe[SMTP:lerobe_at_acxiom.co.uk ]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Tuesday, June 12, 2001 4:00 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Enforced Costraints ??
> >
> > am I missing something here ?? Why would you ever
> need to be in that
> > situation (a table with non-unique values in a PK
> column) ?
> >
> > Apologies if this is a no-brainer.
> >
> > Lee
> >
> >
> > -----Original Message-----
> > Sent: 12 June 2001 09:41
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > i think the solution is to .....
> >
> > (on a table with non-unique values in a PK
> candidate column)
> >
> > 1) create a non-unique index on the pk candidate
> colunm
> > 2) create the pk using enable novalidate clause
> >
> > this way the existing data will NOT checked for
> uniqueness, the constraint
> > will be "enforced" for the upcoming data only...
> >
> > Regards
> > Rahul
> >
> >
> > > > ----------
> > > > From: Anshumn[SMTP:anshumn.sagar_at_wipro.com]
> > > > Sent: Tuesday, June 12, 2001 12:30 PM
> > > > To: Rahul
> > > > Subject: Re: Enforced Costraints ??
> > > >
> > > > Hi Rahul,
> > > >
> > > > That is true. In Oracle 8, there is option to
> enable the constraint in
> > > > novalidate mode or validate mode. The
> novalidate mode is the enforce
> > > mode,
> > > > where only the forthcoming data is checked. It
> does not check the
> > > exisitng
> > > > data. The validate mode is the normal
> constraint enable mode.
> > > >
> > > > The syntax is
> > > > Alter table table enable novalidate constraint
> name;
> > > >
> > > > So after I put the constraint in the
> novalidate mode, the uniqueness
> > > will
> > > > be checked only amongst the coming data. But
> if later I set the
> > > constraint
> > > > ti validate mode(enable), then it may give
> error as the loaded data
> > was
> > > > never checked for uniqueness with the exisitng
> data. In this case I am
> > > > very much confused with the usefulness of this
> feature.
> > > > Can you please give any inputs for the same to
> clear my doubts ?
> > > >
> > > > Thanks & Regards,
> > > > Anshumn
> > > >
> > > > Rahul wrote:
> > > >
> > > > Anshuman,
> > > >
> > > > whenever u add a constraint to a table,
> oracle will make sure all
> > > > the data
> > > > (if any)
> > > > confirms to the constraint added....
> > > >
> > > > you can enable or disable a constraint (even
> defer)... dont confuse
> > > > urself
> > > > with the term "enforcing a constraint".
> > > >
> > > > Regards
> > > > Rahul
> > > >
> > > > > ----------
> > > > > From:
> Anshumn[SMTP:anshumn.sagar_at_wipro.com]
> > > > > Sent: Tuesday, June 12, 2001 11:26
> AM
> > > > > To: LazyDBA.com Discussion
> > > > > Subject: Enforced Costraints ??
> > > > >
> > > > > Hi gurus,
> > > > >
> > > > > Please help me to know the difference
> between enabling and
> > > > enforcing a
> > > > > constraint. I need it urgently.
> > > > >
> > > > > As per my knowledge, enabling a constraint
> checks for the
> > > > > existing data to apply that constraint and
> then the constraint is
> > > > > applied for the forthcoming data. Whereas
> enforcing a constraint
> > > > does
> > > > > not check the existing records and the
> constraint is only applied
> > > > to the
> > > > >
> > > > > forthcoming data only. Is it correct ?
> > > > >
> > > > > Suppose I have a 'unique' constraint. If I
> enforce this
> > > > constraint, then
> > > > >
> > > > > for checking the uniqueness criteria, will
> not it check the
> > > > existing
> > > > > records to determine whether the coming
> record is unique or not ?
> > > > If it
> > > > > checks for the uniqueness among the
> forthcoming records only, then
> > > > I
> > > > > have a doubt. In this case I may have
> loaded a record, with a
> > > > value
> > > > > which is already present in the existing
> data(Since the existing
> > > > data
> > > > > was not checked while enforcing the
> constraint). Now if I enable
> > > > the
> > > > > constraint, then that time it will check
> all the records for
> > > > uniqueness.
> > > > >
> > > > > How does it work in that case ? Won't it
> give me an error ? Then
> > > > how
> > > > > good is the use of an enforced constraint ?
>
> > > > >
> > > > > Please give your valuable feedback to clear
> this doubt. I need it
> > > > > urgently.
> > > > >
> > > > > Thanks in advance,
> > > > > Anshumn
> > > > >
> > > >
> > > >
> > > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > > --
> > > Author: Rahul
> > > INET: rahul_at_ratelindo.co.id
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > > San Diego, California -- Public Internet
> access / Mailing Lists
>
=== message truncated ===
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
INET: R.Daemen_at_facent.nl
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Jun 12 2001 - 07:49:56 CDT
