Re: unique keys

The solution would require some locking to make sure two users don't get the same number returned. Rob Acraman <racraman@ozemail.com.au> wrote: :Barrou Diallo wrote: :> :> Dear Informix/Illustra users. :> :> I have a problem to create rows with unique keys: I have created a :> table like this: :> :> * create table pat :> :> ( :> idpat integer not null primary key, :> firstname text, -- Fisrt Letter Upper case, others in :> lower :> lastname text, -- Fisrt Letter Upper case, others in :> lower :> sex char, -- F|f or M|m :> birthday date, -- Patient birthday :> handedness char, -- L|l= Left R|r= Right :> secunum char(20), -- Securite social num :> info geninfo_t, -- general informations :> :> check ( handedness='L' or handedness='R' :> or handedness='l' or handedness='r'), :> check (sex='M' or sex='F' or sex='m' or sex='f') :> :> ); :> :> When I insert a new row manually, there is no problems: :> :> * insert into pat(idpat,firstname,lastname,sex,handedness) :> values (0,'DIALLO','Barrou','M','R'); :> :> one row inserted :> :> But when I attempt to insert automatically a new roaw with a incremental :> idpat, the system fails : :> :> * insert into pat(idpat) :> select MAX(idpat)+1 from pat; :> XQ0002:Optimizer Internal Error: unknown node type :> :> Have anyone an idea to correct this problem , or an other solution to :> generate a unique key automaticlly. :> :> Thank you very much for your help. : :Hi Barrou, : :Unfortunately, Illustra doesn't have a serial-number-generator (as :you've already found out!). : :First off, for your example you need a 'unique' (since 'select' by :itself returns setof(integer), which is not an integer even when just :one row is returned). : :The 'standard' solution is to embed this into a function - eg: :create function next_idpat :returns integer :as select unique MAX(idpat)+1 from idpat; : :This will allow you to easily change how you allocate serial numbers in :future (eg. change to use a serial-number table). : :Hope this helps, : :Rob Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org