Re: Enforced Costraints ??

On Tuesday 12 June 2001 02:55, Rahul wrote:
> DSS !
Efficient DSS will also have numeric generated PK columns in the dimension tables.

Jared

> > ----------
> > 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
> > > --------------------------------------------------------------------
> > > 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: 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
> > --------------------------------------------------------------------
> > 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).
> >
> >
> > The information contained in this communication is
> > confidential, is intended only for the use of the recipient
> > named above, and may be legally privileged. If the reader
> > of this message is not the intended recipient, you are
> > hereby notified that any dissemination, distribution or
> > copying of this communication is strictly prohibited.
> > If you have received this communication in error, please
> > re-send this communication to the sender and delete the
> > original message or any copy of it from your computer
> > system.
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Robertson Lee - lerobe
> > INET: lerobe_at_acxiom.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: Jared Still
  INET: jkstill_at_cybcon.com

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).