|
HSQLDB example source code file (TestSelfAlterColumn.txt)
The HSQLDB TestSelfAlterColumn.txt source codedrop table "testac" if exists; -- tests for various column definition statements create table "testac" ("a" integer, "b" integer); insert into "testac" values(6,6); insert into "testac" values(5,5); /*r2*/select count(*) from "testac" -- add column without COLUMN keyword alter table "testac" add "c" varchar(10) /*e*/alter table "testac" add "c" varchar(10) alter table "testac" alter column "c" varchar(2) /*e*/insert into "testac" values(5,5,'long string'); insert into "testac" values(4,4,'ls'); /*r 6,6,NULL 5,5,NULL 4,4,ls */select * from "testac" order by "a" desc -- drop column without COLUMN keyword alter table "testac" drop "c" /*r 6,6 5,5 4,4 */select * from "testac" order by "a" desc -- add column with COLUMN keyword alter table "testac" add column "c" varchar(10) default 'XX' not null before "b" /*e*/alter table "testac" add column "c" varchar(10) before "b" /*r 6,XX,6 5,XX,5 4,XX,4 */select * from "testac" order by "a" desc; -- drop column with COLUMN keyword alter table "testac" drop column "c"; /*r 6,6 5,5 4,4 */select * from "testac" order by "a" desc; -- add column without COLUMN keyword - unquoted alter table "testac" add c varchar(10); /*r 6,6,NULL 5,5,NULL 4,4,NULL */select * from "testac" order by "a" desc; -- drop column without COLUMN keyword - unquoted alter table "testac" drop c; /*r 6,6 5,5 4,4 */select * from "testac" order by "a" desc; -- -- PK definition not allowed in alter column /*e*/alter table "testac" alter column "a" integer primary key; /*e*/alter table "testac" alter column "a" integer identity; alter table "testac" add primary key("a"); /*e*/insert into "testac" values (null,7); -- IDENTITY can be added to an existing PK column alter table "testac" alter column "a" integer identity insert into "testac" values (null,7); insert into "testac" ("b") values (8); /*r 4,4 5,5 6,6 7,7 8,8 */select * from "testac"; select * into "testacc" from "testac"; -- IDENTITY can be dropped from PK column alter table "testac" alter column "a" integer; /*e*/insert into "testac" values (null,9); insert into "testac" values (9,9); /*r 4,4 5,5 6,6 7,7 8,8 9,9 */select * from "testac"; alter table "testac" drop primary key; /*e*/insert into "testac" values (null,7); -- column with a PK constraint can be added alter table "testac" add column "c" integer generated by default as identity primary key -- no second IDENTITY or PK /*e*/alter table "testac" add column "d" integer generated by default as identity primary key /*e*/alter table "testac" add column "d" integer generated by default as identity /*e*/alter table "testac" add column "d" integer primary key; -- column with a PK constraint can be dropped alter table "testac" drop "c"; alter table "testac" add unique("a"); /*e*/insert into "testac" values(9,9); alter table "testac" drop "a"; /*r 4 5 6 7 8 9 */select * from "testac"; -- tests for changing column size alter table "testacc" add column "c" varchar(4) default 'aa' not null; update "testacc" set "c" = "c" || cast("a" as varchar); alter table "testacc" add primary key ("c") /*r 4,4,aa4 5,5,aa5 6,6,aa6 7,7,aa7 8,8,aa8 */select * from "testacc"; /*e*/insert into "testacc" values (9,9,'aa9000000'); alter table "testacc" alter column "c" varchar(10) not null; alter table "testacc" alter column "c" varchar(10) not null; insert into "testacc" values (9,9,'aa9000000'); /*e*/insert into "testacc" values (10,10,null); /*e*/alter table "testacc" alter column "c" set null; alter table "testacc" drop primary key; alter table "testacc" alter column "c" set null; insert into "testacc" values (10,10,null); Other HSQLDB examples (source code examples)Here is a short list of links related to this HSQLDB TestSelfAlterColumn.txt source code file: |
... this post is sponsored by my books ... | |
#1 New Release! |
FP Best Seller |
Copyright 1998-2021 Alvin Alexander, alvinalexander.com
All Rights Reserved.
A percentage of advertising revenue from
pages under the /java/jwarehouse
URI on this website is
paid back to open source projects.