RE: Enforced Costraints ??

Rao,

Are you asking what problems could arise if duplicate primary keys exist?

How about:

• Which record is the true parent for child records.
• If you have duplicate parent records, and you wish to update say the name, do you update both records?
• Any join between the parent and child table will result in too many records being returned, leading to additional processing steps to report distinct records.

What you have allowed is a flat file system. Someone might ask the question - why pay for Oracle when you really do not want a relational design.

Pretty silly expense if you ask me.

Just my opinion. Hope this helps.

Tom Mercadante
Oracle Certified Professional

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

In a DSS environment, it is a standard practice to create surrogate keys for primary key. I never had experience with this (having duplicates in PK). Perhaps a list member could tell what probs might arise.

Rao

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

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
> > --------------------------------------------------------------------
> > 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: 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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rao, Maheswara
  INET: Maheswara.Rao_at_Sungardp3.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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