| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Enforced Costraints ??
Rachel,
I was AGREEING with you!! :P I am going to leave it alone.
Chris
-----Original Message-----
Sent: Tuesday, June 12, 2001 2:31 PM
To: Multiple recipients of list ORACLE-L
Chris,
I was AGREEING with you! :) use login and datetime for the key.
Rachel
>From: "Grabowy, Chris" <cgrabowy_at_fcg.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Enforced Costraints ??
>Date: Tue, 12 Jun 2001 09:21:22 -0800
>
>Ok, so I was over analyzing the PK on this table. (big grin)
>
>I still don't see a need for a surrogate key for this table.
>
>Chris
>
>-----Original Message-----
>Sent: Tuesday, June 12, 2001 11:08 AM
>To: Multiple recipients of list ORACLE-L
>
>
>If you remove the datetime column you are going to get errors that the
>unique constraint on the primary key is violated once someone tries to
>login
>
>more than once and is denied.
>
>Composite primary keys are fine, since you will not be updating these
>columns. there is no need in this case for an artificial key. In fact, the
>composite key will be more random and therefore the index created for the
>primary key will be more balanced than if you used a sequence.
>
>my $.02
>
>Rachel
>
>
> >From: "Grabowy, Chris" <cgrabowy_at_fcg.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: RE: Enforced Costraints ??
> >Date: Tue, 12 Jun 2001 06:42:01 -0800
> >
> >I have no real solid design experience, but in the current model that I
>am
> >working on I created this table...
> >
> > AUDIT DENIED LOGONS
> > --------------------
> >| ADL Userid |
> >| ADL DateTime |
> >|--------------------|
> >| |
> > --------------------
> >
> >The two columns in the table are the PK. I have been debating back and
> >forth about this. I wonder if I should remove ADL DateTime from the PK.
> >Still debating.
> >
> >As for a surrogate key, it just didn't seem to need one. Does it? What
> >for?
> >
> >At any rate, DBA stuff is easy compared to design work. I find myself
> >arguing about every single little detail, it's driving me nuts.
> >
> >Any thoughts or arguments or ideas?
> >
> >Many thanks.
> >
> >Chris
> >
> >-----Original Message-----
> >Sent: Tuesday, June 12, 2001 10:12 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >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-----
> >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: Grabowy, Chris
> > INET: cgrabowy_at_fcg.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).
>
>_________________________________________________________________
>Get your FREE download of MSN Explorer at http://explorer.msn.com
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Rachel Carmichael
> INET: carmichr_at_hotmail.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: Grabowy, Chris
> INET: cgrabowy_at_fcg.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: Rachel Carmichael INET: carmichr_at_hotmail.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: Grabowy, Chris INET: cgrabowy_at_fcg.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 - 12:45:25 CDT
 |
 |