Path: utzoo!utgpu!jarvis.csri.toronto.edu!rutgers!usc!cs.utexas.edu!uunet!mcvax!hp4nl!rivm!llojd From: llojd@rivm.UUCP (J.W. Diesel) Newsgroups: comp.databases Subject: Re: Attn: RTIngres GURUs Summary: INFORMIX serial ??!! Message-ID: <1389@rivm05.UUCP> Date: 27 Jun 89 14:45:53 GMT References: <449@sdcc15.ucsd.edu> <2981@rtech.rtech.com> Organization: RIVM, Bilthoven, The Netherlands Lines: 61 In article , emuleomo@yes.rutgers.edu (Emuleomo) writes: > > Just as a quick followup on my previous question > > IS THERE A WAY TO IMPLEMENT a SERIAL fld in Ingres (as is done in Informix)?? > > A serial fld is a fld which has ALL the following properties. > (AS defined by Informix software) > a) It uniquely identifies a row in a table. i.e. it is a key for that row. > b) It is managed by the DBMS AUTOMATICALLY and a unique value is > generated for any new row inserted into that table. > c) It cannot be modified, once generated! > > ** I dont understand why I (a self proclaimed Informix Guru) am having _such_ > a hard time with Ingres. Maybe I should stop thinking in Informix! > However, I thought that SQL is SQL is SQL! and RDBMS is RDBMS is RDBMS. > > Respondez S'Il vouz plait. > > --Emuleomo O.O. (emuleomo@yes.rutgers.edu) > -- > ** Research is what I'm doing when I dont know what I'm doing! ** As I would not want other INFORMIX gurus :=) to be mislead I cannot resist the temptation of reacting upon Emuleomo's interpretation of the definition of the informix serial datatype. a) To my experience the serial datatype DOES NOT enforce unique values (only a unique index will do this): on insertion of a row containing a serial column you may, optionally, enter an 'initial' value. This value will be used wether or not it value is unique !!! b) DBMS management means that, unless an 'initial value' is given, the inserted value is the maximum-value incremented by one. Informix seems to 'remember' the maximum value somewhere since deletion of row(s) containing the maximum value doesnot change the value to be assigned to future 'serial' insertions. c) For those who want to update a serial column: alter the datatype to integer, perform your updates and alter the column back to serial. I'm not sure if the 'remembered' maximum-value is affected by this action. Though I'm not familiar with INGRES-SQL you might consider the following scheme to insert unique values: INSERT INTO yourtable (serial_column) SELECT MAX(serial_column) + 1 FROM yourtable; Informix does not permit you to use the insert-table in the from clause, maybe INGRES does. You may of course use a temporary table to store the maxvalue but that would make you pay a considerable performance penalty *and* it can cause consistency problems: process A determines max; process B determines max; process A inserts serial (=max + 1); process B inserts serial ==> A and B insert same serial !!! -------------------------- Jan Diesel National Institute for Public Health and Environment Laboratory for Air Research mcvax!rivm!llojd -------------------------- Wonder how he got there with that name !!!!