Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Enforced Costraints ??

RE: Enforced Costraints ??

From: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Tue, 12 Jun 2001 08:44:26 -0700
Message-ID: <F001.0032579C.20010612070741@fatcity.com>

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).
Received on Tue Jun 12 2001 - 10:44:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US