Re: unique keys
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_idpatreturns 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