[Info-ingres] One up unique number for an attribute - in SQL - does anyone know the trick of doing this?

[Paul White]

Hi Allan,

 

Here is an example which uses automatic system sequences for master detail
relationship. (mind your transaction isolation).

 

\continue

set autocommit on;

\p\g

 

drop table master;

drop table detail;

\p\g

 

create table master(

col1 integer GENERATED ALWAYS AS IDENTITY,

col2 varchar(20));

\p\g

 

create table detail(

col1 integer NOT NULL NOT DEFAULT,

col2 varchar(20));

\p\g

 

insert into master(col2) values ('this is master1');

\p\g

 

insert into detail(col1,col2) values (last_identity(),'this is detail1-1');

insert into detail(col1,col2) values (last_identity(),'this is detail1-2');

insert into detail(col1,col2) values (last_identity(),'this is detail1-3');

insert into detail(col1,col2) values (last_identity(),'this is detail1-4');

\p\g

 

insert into master(col2) values ('this is master2');

\p\g

 

insert into detail(col1,col2) values (last_identity(),'this is detail2-1');

insert into detail(col1,col2) values (last_identity(),'this is detail2-2');

insert into detail(col1,col2) values (last_identity(),'this is detail2-3');

\p\g

 

select * from master;

select * from detail;

\p\g

 

 

│col1         │col2                │

│            1│this is master1     │

│            2│this is master2     │

 

│col1         │col2                │

│            1│this is detail1-1   │

│            1│this is detail1-2   │

│            1│this is detail1-3   │

│            1│this is detail1-4   │

│            2│this is detail2-1   │

│            2│this is detail2-2   │

│            2│this is detail2-3   │

 

 

Cheers

 

Paul

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20170207/3dcf5545/attachment.html>