📄 autoincrement.out
字号:
ij> -- Adding new testcases for DB2 syntax "GENERATED ALWAYS AS IDENTITY"-- We don't enhance "ALTER TABLE <T> MODIFY COLUMN" yet: DB2 uses "ALTER TABLE <T> ALTER COLUMN..."-- try generated values with all types.-- Cloudscape specific syntax for the autoincrement clause can be found in store/bug3498.sqlcreate table ai_zero (i int, a_zero int generated always as identity);0 rows inserted/updated/deletedij> create table ai_one (i int, a_one smallint generated always as identity);0 rows inserted/updated/deletedij> create table ai_two (i int, a_two int generated always as identity);0 rows inserted/updated/deletedij> create table ai_three (i int, a_three int generated always as identity);0 rows inserted/updated/deletedij> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME in ('A_ZERO', 'A_ONE', 'A_TWO', 'A_THREE');COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------A_ZERO |1 |1 |1 A_ONE |1 |1 |1 A_TWO |1 |1 |1 A_THREE |1 |1 |1 ij> drop table ai_zero;0 rows inserted/updated/deletedij> drop table ai_one;0 rows inserted/updated/deletedij> drop table ai_two;0 rows inserted/updated/deletedij> drop table ai_three;0 rows inserted/updated/deletedij> -- try a generated column spec with initial and start values.create table ai (i int, autoinc int generated always as identity (start with 100));0 rows inserted/updated/deletedij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'AUTOINC';AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC --------------------------------------------------------------100 |100 |1 ij> drop table ai;0 rows inserted/updated/deletedij> create table ai (i int, autoinc int generated always as identity (increment by 100));0 rows inserted/updated/deletedij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'AUTOINC';AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC --------------------------------------------------------------1 |1 |100 ij> drop table ai;0 rows inserted/updated/deletedij> create table ai (i int, autoinc int generated always as identity (start with 101, increment by 100));0 rows inserted/updated/deletedij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'AUTOINC';AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC --------------------------------------------------------------101 |101 |100 ij> drop table ai;0 rows inserted/updated/deletedij> -- try -ive numbers.create table ai1 (i int, a1 int generated always as identity (start with 0, increment by -1));0 rows inserted/updated/deletedij> create table ai2 (i int, a2 int generated always as identity (start with +0, increment by -1));0 rows inserted/updated/deletedij> create table ai3 (i int, a3 int generated always as identity (start with -1, increment by -1));0 rows inserted/updated/deletedij> create table ai4 (i int, a4 int generated always as identity (start with -11, increment by +100));0 rows inserted/updated/deletedij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'A1';AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC --------------------------------------------------------------0 |0 |-1 ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'A2';AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC --------------------------------------------------------------0 |0 |-1 ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'A3';AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ---------------------------------------------------------------1 |-1 |-1 ij> select AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'A4';AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC ---------------------------------------------------------------11 |-11 |100 ij> drop table ai1;0 rows inserted/updated/deletedij> drop table ai2;0 rows inserted/updated/deletedij> drop table ai3;0 rows inserted/updated/deletedij> drop table ai4;0 rows inserted/updated/deletedij> -- **** simple increment tests.-- should return null as no single insert has been executedvalues IDENTITY_VAL_LOCAL();1 -------------------------------NULL ij> create table ai_short (i int, ais smallint generated always as identity (start with 0, increment by 2));0 rows inserted/updated/deletedij> insert into ai_short (i) values (0);1 row inserted/updated/deletedij> insert into ai_short (i) values (1);1 row inserted/updated/deletedij> insert into ai_short (i) values (2);1 row inserted/updated/deletedij> insert into ai_short (i) values (3);1 row inserted/updated/deletedij> select * from ai_short;I |AIS ------------------0 |0 1 |2 2 |4 3 |6 ij> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINCfrom sys.syscolumns where COLUMNNAME = 'AIS';COLUMNNAME |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------AIS |8 |0 |2 ij> drop table ai_short;0 rows inserted/updated/deletedij> -- table with more than one generated column spec should failcreate table ai_multiple (i int, a0 int generated always as identity (start with -1, increment by -1), a1 smallint generated always as identity, a2 int generated always as identity (start with 0), a3 bigint generated always as identity (start with -100, increment by 10));ERROR 428C1: Only one identity column is allowed in a table.ij> -- table with one generated column spec should succeedcreate table ai_single1 (i int, a0 int generated always as identity (start with -1, increment by -1));0 rows inserted/updated/deletedij> create table ai_single2 (i int, a1 smallint generated always as identity);0 rows inserted/updated/deletedij> create table ai_single3 (i int, a2 int generated always as identity (start with 0));0 rows inserted/updated/deletedij> create table ai_single4 (i int, a3 bigint generated always as identity (start with -100, increment by 10));0 rows inserted/updated/deletedij> insert into ai_single1 (i) values (1);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (2);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (3);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (4);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (5);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (6);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (7);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (8);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (9);1 row inserted/updated/deletedij> insert into ai_single1 (i) values (10);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (1);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (2);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (3);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (4);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (5);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (6);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (7);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (8);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (9);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (10);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (1);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (2);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (3);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (4);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (5);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (6);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (7);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (8);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (9);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (10);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (1);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (2);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (3);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (4);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (5);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (6);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (7);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (8);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (9);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (10);1 row inserted/updated/deletedij> select a.i, a0, a1, a2, a3 from ai_single1 a join ai_single2 b on a.i = b.i join ai_single3 c on a.i = c.i join ai_single4 d on a.i = d.i;I |A0 |A1 |A2 |A3 ---------------------------------------------------------------1 |-1 |1 |0 |-100 2 |-2 |2 |1 |-90 3 |-3 |3 |2 |-80 4 |-4 |4 |3 |-70 5 |-5 |5 |4 |-60 6 |-6 |6 |5 |-50 7 |-7 |7 |6 |-40 8 |-8 |8 |7 |-30 9 |-9 |9 |8 |-20 10 |-10 |10 |9 |-10 ij> delete from ai_single1;10 rows inserted/updated/deletedij> delete from ai_single2;10 rows inserted/updated/deletedij> delete from ai_single3;10 rows inserted/updated/deletedij> delete from ai_single4;10 rows inserted/updated/deletedij> insert into ai_single1 (i) values (1);1 row inserted/updated/deletedij> insert into ai_single2 (i) values (1);1 row inserted/updated/deletedij> insert into ai_single3 (i) values (1);1 row inserted/updated/deletedij> insert into ai_single4 (i) values (1);1 row inserted/updated/deletedij> select a.i, a0, a1, a2, a3 from ai_single1 a join ai_single2 b on a.i = b.i join ai_single3 c on a.i = c.i join ai_single4 d on a.i = d.i;I |A0 |A1 |A2 |A3 ---------------------------------------------------------------1 |-11 |11 |10 |0 ij> -- clean updrop table ai_single1;0 rows inserted/updated/deletedij> drop table ai_single2;0 rows inserted/updated/deletedij> drop table ai_single3;0 rows inserted/updated/deletedij> drop table ai_single4;0 rows inserted/updated/deletedij> -- **** connection info tests {basic ones}create table ai_test (x int generated always as identity (start with 2, increment by 2), y int);0 rows inserted/updated/deletedij> insert into ai_test (y) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);10 rows inserted/updated/deletedij> -- should see 20.values IDENTITY_VAL_LOCAL();1 -------------------------------0 ij> insert into ai_test (y) select y+10 from ai_test;10 rows inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------0 ij> values IDENTITY_VAL_LOCAL();1 -------------------------------0 ij> -- try some more connection info testscreate table ai_single1 (c char(100), a_odd int generated always as identity (start with 1, increment by 2));0 rows inserted/updated/deletedij> create table ai_single2 (c char(100), a_even int generated always as identity (start with 0, increment by 2));0 rows inserted/updated/deletedij> create table ai_single3 (c char(100), a_sum bigint generated always as identity (start with 1, increment by 2));0 rows inserted/updated/deletedij> insert into ai_single1 (c) values ('a');1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------1 ij> insert into ai_single2 (c) values ('a');1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------0 ij> insert into ai_single3 (c) values ('a');1 row inserted/updated/deletedij> values IDENTITY_VAL_LOCAL();1 -------------------------------⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -