Re: Reset values in serial datatype

> Subject: Reset values in serial datatype > Date: 23 Feb 1995 05:51:06 GMT > Reply-To: dbaresrc@xmission.xmission.com (DBA Resources) > Organization: XMission Public Access Internet (801 539 0900) > > I thought I had read in here a discussion about resetting the starting > values of a serial datatyped column. I just got finished looking through > the indexes of this newsgroup at mathcs.emory.edu and found nothing. > ...omitted... > > In this case I have a significant number of tables that refer to these > columns via foreign keys and when I alter the datatype all the constraints > go south. > > Is there a way of resetting the value of a serial datatype back to 0?? > > Thanks for any help. > -- > Carlton Doe "It's not *over* until I win!" > DBA Resources, Inc. -- Les Brown > Salt Lake City, UT > carlton@errin.dbaresrc.com http://xmission.com/~dbaresrc Unfortunately, what I am about to suggest will not help Carlton in his current situation. However, in hopes of avoiding or reducing this problem in the future, I offer the following technique: 1. NEVER have a serial column in a table having real data. Use integer instead. Then apply foreign key and other constraints to the integer columns. 2. Have a one-column table for each serial sequence needed. Use a function to fetch the next serial value, which is then used as the (integer) primary key of the table that would otherwise have the serial column as its primary key. Integers as foreign keys are unaffected. The function can be written to always reduce the table back to one row or to retain rows for statistical purposes (how many new keys were added during this period?) PROS: 1. Solves problems such as Carltons. Just drop and recreate the key generating table to reset the serial. Constraints in other tables are not affected. 2. Product independence. Serial columns are a non-standard extension to SQL provided by Informix. In the unlikely event you need to port your application to a different DB product, this design will be easier to port. For example, Oracle uses something called "sequences" to generate keys. The function to fetch next key could be replaced, but the logic of the "real data" tables would be completely unaffected. 3. Better logical design. Separation of key-generating from key-storing functions leads to clearer programs. It is somewhat unnatural to: a: store a zero in a master record, where you know a different value will be generated by the engine, b: extract the value generated from what is otherwise an error reporting structure, c: store the extracted value in the detail records. Hiding all the foolishness in a key generating function and then storing the real key with both the master and detail records makes more sense when reading the program, such as during maintenance or upgrade. CONS: 1. Slightly more tables in the database. This will require a small amount of extra space in the system catalogs and in the space used to hold the key generator tables. 2. Slightly reduced speed. Adding a row with a new key requires one additional database access, compared with having the key in the "real" table. I have used the above approach in several projects with great success. The cost is negligible. The extra space required is limited to a few hundred bytes per key. The extra access is seldom, if ever, noticeable to the user. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\