> One thing to watch out for is the actual use of IDENTITY as a FK column
> in another table. If you are doing this, you have a disaster waiting to
> happen... (true for any DB, SQLSERVER,ORACLE,SYBASE, etc)
Why is this a 'disaster waiting to happen'?
> -----Original Message-----
> From: Stephen Garrett [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 11, 2000 11:39 AM
> To: [EMAIL PROTECTED]
> Subject: RE: What are you using instead of IDENTITY?
>
>
>
> One thing to watch out for is the actual use of IDENTITY as a FK column
> in another table. If you are doing this, you have a disaster waiting to
> happen... (true for any DB, SQLSERVER,ORACLE,SYBASE, etc)
>
>
> Steve
>
>
> At 10:43 AM 7/11/2000 -0400, Bryan Batchelder wrote:
> >This message is in MIME format. Since your mail reader does not
> understand
> >this format, some or all of this message may not be legible.
> >
> >------_=_NextPart_001_01BFEB46.64A75130
> >Content-Type: text/plain;
> > charset="iso-8859-1"
> >
> >I was always taught that the primary key for a table should
> never be related
> >in any way to your data. A specific example would be to assume
> that social
> >security numbers are indeed globally unique (they are not), you
> would still
> >not want to use them as the primary key in a table of
> people.....because the
> >key is related to the data it uniquely represents....
> >
> >I have no problem with using timestamps, as long as the
> database, operating
> >system, and datatype being used have the resolution to avoid
> duplicate keys
> >when large numbers of rows are being inserted at the same time.
> >
> >I am no relational database expert by any means, but my paradigm
> has worked
> >for me so far (and almost everyone I know has the same view of identity
> >columns)
> >
> >--b
> >
> >--------------------------------------------
> >Bryan D. Batchelder Work: 813-935-7100
> >Palm/Internet Developer Home: 727-547-1322
> >--------------------------------------------
> >ConnectWise, Inc. (www.ConnectWise.com)
> >2803 West Busch Blvd, Suite 204
> >Tampa, FL 33618
> >--------------------------------------------
> >
> >-----Original Message-----
> >From: Tom Nunamaker [mailto:[EMAIL PROTECTED]]
> >Sent: Tuesday, July 11, 2000 10:29 AM
> >To: [EMAIL PROTECTED]
> >Subject: What are you using instead of IDENTITY?
> >
> >
> >In Joe Celko's book "SQL for smarties", he blasts IDENTITY and other
> >Generator Functions as "a horrible, nonstandard, nonrelational
> >proprietary extension that should be avoided whenever possible"
> (p 63 if you
> >have the book)
> >
> >I wrote Joe about this with this reply:
> >
> >===========================
> > >> You don't mention why or alternatives. I work with SQL server and the
> >topic of the @@IDENTITY variables comes up over and over. Some
> say use it,
> >others like yourself say avoid it. What arguments can I bring to
> the table
> >against using @@IDENTITY ? <<
> >
> >
> >Not Standard, not portable, and has a lot of weird side effects and
> >behaviors (scan the UseNet and other postings from people having problems
> >with it).
> >
> >For fun, build a table with one column that is an IDENTITY
> column, then try
> >to insert, update and delete from it. You'll find out that it is not a
> >table because it does not behave like a table.
> >
> >Conceptually, it is non-relational. If I insert the sequence {a, b, c}
> >into a table with the IDENTITY column, they get the numbering ((a,1),
> >(b,2), (c,3)), but if I insert the sequence {c, a, b} into a
> table with the
> >IDENTITY column, they get the numbering ((c,1), (a,2), (b,3)) instead.
> >Suddenly, ordering affects the attributes of an entity, in violation of a
> >basic relational principle that says we are using (unordered) sets.
> >
> >For a data modeling viewpoint, why is a number associated at
> ransom with an
> >entity? What does it mean? It means that you could not think of a good
> >way to identify one entity from another and in desperation, you
> faked it!!
> >Timestamping rows at least marks an event.
> >
> >--CELKO--
> >
> >===========================
> >The question to the group is: "What are you using instead of IDENTITY?"
> >Perhaps you are using it and disagree with Joe Celko. What are your
> >thoughts?
> >
> >Tom
> >
> >
> >
> >
> >-----------------------------------------------------------------
> -----------
> >--
> >Archives: http://www.mail-archive.com/[email protected]/
> >To Unsubscribe visit
> >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/c
> f_talk or
> >send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> >the body.
> >
> >------_=_NextPart_001_01BFEB46.64A75130
> >Content-Type: text/html;
> > charset="iso-8859-1"
> >Content-Transfer-Encoding: quoted-printable
> >
> ><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
> ><HTML>
> ><HEAD>
> ><META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
> >charset=3Diso-8859-1">
> ><META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
> >5.5.2650.12">
> ><TITLE>RE: What are you using instead of IDENTITY?</TITLE>
> ></HEAD>
> ><BODY>
> >
> ><P><FONT SIZE=3D2>I was always taught that the primary key for a table =
> >should never be related in any way to your data. A specific =
> >example would be to assume that social security numbers are indeed =
> >globally unique (they are not), you would still not want to use them as =
> >the primary key in a table of people.....because the key is related to =
> >the data it uniquely represents....</FONT></P>
> >
> ><P><FONT SIZE=3D2>I have no problem with using timestamps, as long as =
> >the database, operating system, and datatype being used have the =
> >resolution to avoid duplicate keys when large numbers of rows are being =
> >inserted at the same time.</FONT></P>
> >
> ><P><FONT SIZE=3D2>I am no relational database expert by any means, but =
> >my paradigm has worked for me so far (and almost everyone I know has =
> >the same view of identity columns)</FONT></P>
> >
> ><P><FONT SIZE=3D2>--b</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>--------------------------------------------</FONT>
> ><BR><FONT SIZE=3D2>Bryan D. =
> >Batchelder Work: =
> >813-935-7100</FONT>
> ><BR><FONT SIZE=3D2>Palm/Internet Developer Home: =
> >727-547-1322</FONT>
> ><BR><FONT SIZE=3D2>--------------------------------------------</FONT>
> ><BR><FONT SIZE=3D2>ConnectWise, Inc. (www.ConnectWise.com)</FONT>
> ><BR><FONT SIZE=3D2>2803 West Busch Blvd, Suite 204</FONT>
> ><BR><FONT SIZE=3D2>Tampa, FL 33618</FONT>
> ><BR><FONT SIZE=3D2>--------------------------------------------</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>-----Original Message-----</FONT>
> ><BR><FONT SIZE=3D2>From: Tom Nunamaker [<A =
> >HREF=3D"mailto:[EMAIL PROTECTED]">mailto:[EMAIL PROTECTED]</A>]</FONT>
> ><BR><FONT SIZE=3D2>Sent: Tuesday, July 11, 2000 10:29 AM</FONT>
> ><BR><FONT SIZE=3D2>To: [EMAIL PROTECTED]</FONT>
> ><BR><FONT SIZE=3D2>Subject: What are you using instead of =
> >IDENTITY?</FONT>
> ></P>
> ><BR>
> >
> ><P><FONT SIZE=3D2>In Joe Celko's book "SQL for smarties", he =
> >blasts IDENTITY and other</FONT>
> ><BR><FONT SIZE=3D2>Generator Functions as "a horrible, =
> >nonstandard, nonrelational</FONT>
> ><BR><FONT SIZE=3D2>proprietary extension that should be avoided =
> >whenever possible" (p 63 if you</FONT>
> ><BR><FONT SIZE=3D2>have the book)</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>I wrote Joe about this with this reply:</FONT>
> ></P>
> >
> ><P><FONT =
> >SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> >=3D=3D=3D=3D=3D=3D</FONT>
> ><BR><FONT SIZE=3D2> >> You don't mention why or =
> >alternatives. I work with SQL server and the</FONT>
> ><BR><FONT SIZE=3D2>topic of the @@IDENTITY variables comes up over and =
> >over. Some say use it,</FONT>
> ><BR><FONT SIZE=3D2>others like yourself say avoid it. What arguments =
> >can I bring to the table</FONT>
> ><BR><FONT SIZE=3D2>against using @@IDENTITY ? <<</FONT>
> ></P>
> ><BR>
> >
> ><P><FONT SIZE=3D2>Not Standard, not portable, and has a lot of weird =
> >side effects and</FONT>
> ><BR><FONT SIZE=3D2>behaviors (scan the UseNet and other postings from =
> >people having problems</FONT>
> ><BR><FONT SIZE=3D2>with it).</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>For fun, build a table with one column that is an =
> >IDENTITY column, then try</FONT>
> ><BR><FONT SIZE=3D2>to insert, update and delete from it. You'll find =
> >out that it is not a</FONT>
> ><BR><FONT SIZE=3D2>table because it does not behave like a =
> >table.</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>Conceptually, it is non-relational. If I insert the =
> >sequence {a, b, c}</FONT>
> ><BR><FONT SIZE=3D2>into a table with the IDENTITY column, they get the =
> >numbering ((a,1),</FONT>
> ><BR><FONT SIZE=3D2>(b,2), (c,3)), but if I insert the sequence {c, a, =
> >b} into a table with the</FONT>
> ><BR><FONT SIZE=3D2>IDENTITY column, they get the numbering ((c,1), =
> >(a,2), (b,3)) instead.</FONT>
> ><BR><FONT SIZE=3D2>Suddenly, ordering affects the attributes of an =
> >entity, in violation of a</FONT>
> ><BR><FONT SIZE=3D2>basic relational principle that says we are using =
> >(unordered) sets.</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>For a data modeling viewpoint, why is a number =
> >associated at ransom with an</FONT>
> ><BR><FONT SIZE=3D2>entity? What does it mean? It means that you could =
> >not think of a good</FONT>
> ><BR><FONT SIZE=3D2>way to identify one entity from another and in =
> >desperation, you faked it!!</FONT>
> ><BR><FONT SIZE=3D2>Timestamping rows at least marks an event.</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>--CELKO--</FONT>
> ></P>
> >
> ><P><FONT =
> >SIZE=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> >=3D=3D=3D=3D=3D=3D</FONT>
> ><BR><FONT SIZE=3D2>The question to the group is: "What are you =
> >using instead of IDENTITY?"</FONT>
> ><BR><FONT SIZE=3D2>Perhaps you are using it and disagree with Joe =
> >Celko. What are your</FONT>
> ><BR><FONT SIZE=3D2>thoughts?</FONT>
> ></P>
> >
> ><P><FONT SIZE=3D2>Tom</FONT>
> ></P>
> ><BR>
> ><BR>
> ><BR>
> >
> ><P><FONT =
> >SIZE=3D2>---------------------------------------------------------------=
> >---------------</FONT>
> ><BR><FONT SIZE=3D2>Archives: <A =
> >HREF=3D"http://www.mail-archive.com/[email protected]/" =
> >TARGET=3D"_blank">http://www.mail-archive.com/[email protected]/=
> ></A></FONT>
> ><BR><FONT SIZE=3D2>To Unsubscribe visit <A =
> >HREF=3D"http://www.houseoffusion.com/index.cfm?sidebar=3Dlists&body=3Dli=
> >sts/cf_talk" =
> >TARGET=3D"_blank">http://www.houseoffusion.com/index.cfm?sidebar=3Dlists=
> >&body=3Dlists/cf_talk</A> or send a message to =
> >[EMAIL PROTECTED] with 'unsubscribe' in the =
> >body.</FONT></P>
> >
> ></BODY>
> ></HTML>
> >------_=_NextPart_001_01BFEB46.64A75130--
> >-----------------------------------------------------------------
> ----------
> ---
> >Archives: http://www.mail-archive.com/[email protected]/
> >To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
> >
>
> --
> Stephen Garrett
> GPS
> [EMAIL PROTECTED] (360) 896-2714
> ICQ# 10776767
> ------------------------------------------------------------------
> ------------
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf
_talk or send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
