RE: Enforced Costraints ??

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Tue, 12 Jun 2001 06:06:06 -0700
Message-ID: <F001.00325485.20010612061143@fatcity.com>

Well, here's one problem - there is no guarantee that Social Security Number will indeed be unique.  SS#'s are reassigned, just like phone numbers are. 

IMHO, surrogate keys are always a good idea.  Has anyone got an example of when a surrogate key is NOT a good idea?

Lisa Koivu
Oracle Database Administrator
954-935-4117

The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful.

The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments.

-----Original Message-----

From:   Rao, Maheswara [SMTP:Maheswara.Rao_at_Sungardp3.com]
Sent:   Tuesday, June 12, 2001 9:21 AM
To:     Multiple recipients of list ORACLE-L

Subject:        RE: Enforced Costraints ??

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

Received on Tue Jun 12 2001 - 08:06:06 CDT