RE: Enforced Costraints ??

Primary key is "NOT NULL" and "UNIQUE".

Otherwise it isn't a primary key or a primary key canidate

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

-----Original Message-----
Sent: Tuesday, June 12, 2001 5:00 AM
To: Multiple recipients of list ORACLE-L

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: Christopher Spence
  INET: cspence_at_FuelSpot.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).