You know, no matter how many times I have been told that a certain value is
unique, not null, and will never, ever change, and thus should be used as a
primary key, there comes a time that someone decides they need to be
changed, if only because they were entered wrong in the first place.
At 08:06 AM 6/12/01 -0800, you wrote:
>If user id is unique, not null, and never will be or will not change, then
>no need for surrogate or datetime.
>Surrogate keys are great because they are no hassle and easy. They do not
>mutate 10 columns of a primary key into other tables everytime you have a
>relationship with them. Although as someone brought up, sequences are nasty
>things, and they cause a few problems with indexes.
>
>"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 10:42 AM
>To: Multiple recipients of list ORACLE-L
>
>
>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:[EMAIL PROTECTED]]
>Verzonden: dinsdag 12 juni 2001 11:56
>Aan: Multiple recipients of list ORACLE-L
>Onderwerp: RE: Enforced Costraints ??
>
>
>DSS !
>
>
> > ----------
> > From: Robertson Lee - lerobe[SMTP:[EMAIL PROTECTED] ]
> > Reply To: [EMAIL PROTECTED]
> > 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:[EMAIL PROTECTED]]
> > > > 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:[EMAIL PROTECTED]]
> > > > > 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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: Regina Harter
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
