IDENTITY INSERT ON IDENTITY INSERT OFF
That's always let me get around what few problems I've had dealing with systems using identity PK's. Sometimes not ideal, sometimes a couple extra steps do get what you need to get done. I've always looked at identity as an auto sequence generator, and use the above commands to turn the sequence on and off as needed. Now, IMHO, an application should never need to break a sequence. I've never seen a rational reason why it should happen in an application (which is the scope of this forum?). As a sql dba, I have several easy means of breaking that identity / auto-sequence to migrate, reinstate, or repair a data problem. In the end, I make the effort to do what is easiest for the developers, and in most of our projects (10-30 tables with not more than 10k records expected) I use identity FK's, a few apps I insist otherwise, but if I use a GUID PK its only for binding the PK to FK constraints uniquely. _Most importantly_, I still include an identity not in the key as a row counter in addition to the GUID. Typically, in your app code, it's just easier to deal with a logical sequence in a result set. $0.02 Trey Rouse Data Application Architect Web Services - Rice University > -----Original Message----- > From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 29, 2003 10:16 AM > To: CF-Talk > Subject: Re: DB Design > > here are a few things to ponder.... > > identity is not a SQL standard (SQL-92 -- not sql server) > > identity does not exist in Oracle, sybase, informix, db2, or any other > large > db platform. > > identity breaks one of the 4 basic rules of a relational db > --you cannot update the unique and independent keys > --can't update an identity column.... I just have to take the next value > in > line > > also, there are problems with replication using Identities... there are > work-arounds, but they are a pain.... > > Just a few things I have come across from different people. > > Mike > > > > > > > ----- Original Message ----- > From: "Tony Weeg" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Tuesday, July 29, 2003 11:05 AM > Subject: RE: DB Design > > > > id love to know the outcome of this one....since we use Identity PK's > > like they are going out of style :) > > > > tony weeg > > uncertified advanced cold fusion developer > > tony at navtrak dot net > > www.navtrak.net > > office 410.548.2337 > > fax 410.860.2337 > > > > > > -----Original Message----- > > From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, July 29, 2003 10:56 AM > > To: CF-Talk > > Subject: Re: DB Design > > > > > > I welcome the discussion but back it up.. > > > > PITA? In what ways? > > > > > > ----- Original Message ----- > > From: "Boardwine, David L." <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Tuesday, July 29, 2003 10:50 AM > > Subject: RE: DB Design > > > > > > > Ok, I'll give my .02 worth. BS. What am I supposed to use as a PK? > > > GUIDs? GUIDs are what M$ uses and they are a PITA. DavidB > > > > > > > > > -----Original Message----- > > > From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] > > > Sent: Tuesday, July 29, 2003 10:48 AM > > > To: CF-Talk > > > Subject: SOT: DB Design > > > > > > > > > I am working on a new DB design for a CFMX app and was doing a little > > > refresher research on keys and data types and ran across this quote > > > from former SQL Server project manager Ron Soukup, > > > > > > "Identity primary keys are for people who believe there's never time > > > to design a table right but there's always time to do it over." > > > > > > In another related article, another MS SQL guy says that the only > > > reason identity made it into SQL server was because of Access.... (not > > > > > a direct quote). > > > > > > Anyone care to comment? > > > > > > Mike > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
