Re: Unique row identifiers... (esp. Informix)
Path: emory!swrinde!zaphod.mps.ohio-state.edu!cis.ohio-state.edu!ucbvax!hplabs!nsc!pyramid!infmx!news From: cortesi@informix.com (David Cortesi) Newsgroups: comp.databases Keywords: triggers Informix serial Message-ID: <1991Aug12.152207.6020@informix.com> Date: 12 Aug 91 15:22:07 GMT References: <1991Aug9.211421.21614@cbnewsk.att.com> Sender: news@informix.com (Usenet News) Reply-To: cortesi@informix.com Organization: Informix Software, Inc. > j.f.van valkenburg quotes Lance Hartmann who wrote: > > Does anyone "know" how Informix implements this [i.e. unique row ids]? > > Informix supports a data type for tables called "serial" which does the > > automatic numbering of rows. Does it pull this number some how from > > internal row information that it stores? From a cursory scan, I didn't > > see anything in the sys* tables to store this kind of info... >and then speculates, > Since Informix keeps a silent field called rowid, this > assignes a number to each and every row in the database. > When you assign a serial field it asks for the starting serial > number, defaults to 1. It appears that this rowid is added to > the serial seed. Not so. The SERIAL datatype and the ROWID values are completely unrelated and it can be dangerous to confuse them. As Hartmann thought, the "next" value for a SERIAL column is stored in an internal structure that describes each table. It's a long that is incremented each time a new serial value is needed. A SERIAL column is an actual column in the table, not a "virtual" column and in no way related to physical addressing. ROWID, on the other hand, is a physical number with no promised numerical qualities other than uniqueness, i.e. it doesn't have to be monotonically increasing or change by a constant amount. ROWID is not a real column in the table but merely the value of the row's physical address. Hence it can change if you do something that moves the table on disk, like altering it to add a column, so you should never store ROWID as a foreign key column. Hope this clarifies things, Dave Cortesi