📄 primarykey.out
字号:
ij> select * from pos1;C1 |C2 -----------------------1 |2 ij> delete from pos1;1 row inserted/updated/deletedij> select * from pos1;C1 |C2 -----------------------ij> -- create a table with lots key columnscreate table pos2 (i int not null, s smallint not null, r real not null, dp double precision not null, c30 char(30) not null, vc10 varchar(10) not null, d date not null, t time not null, ts timestamp not null, primary key(ts, t, d, vc10, c30, dp, r, s, i));0 rows inserted/updated/deletedij> insert into pos2 values(111111, 1, 1.11, 11111.1111, 'char(30)', 'vc(10)', '1999-9-9', '8:08:08', '1999-9-9 8:08:08');1 row inserted/updated/deletedij> insert into pos2 values(111111, 1, 1.11, 11111.1111, 'char(30)', 'vc(10)', '1999-9-9', '8:08:08', '1999-9-9 8:08:08');ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'POS2'.ij> -- verify the consistency of the indexes on the system catalogsselect tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename)from sys.systables where tabletype = 'S' and tablename != 'SYSDUMMY1';TABLENAME |2 --------------------------------------------------------------------------------------------------------------------------------------------SYSCONGLOMERATES |1 SYSTABLES |1 SYSCOLUMNS |1 SYSSCHEMAS |1 SYSCONSTRAINTS |1 SYSKEYS |1 SYSDEPENDS |1 SYSALIASES |1 SYSVIEWS |1 SYSCHECKS |1 SYSFOREIGNKEYS |1 SYSSTATEMENTS |1 SYSFILES |1 SYSTRIGGERS |1 SYSSTATISTICS |1 ij> -- drop tablesdrop table pos1;0 rows inserted/updated/deletedij> drop table pos2;0 rows inserted/updated/deletedij> -- verify the consistency of the indexes on the system catalogsselect tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename)from sys.systables where tabletype = 'S' and tablename != 'SYSDUMMY1';TABLENAME |2 --------------------------------------------------------------------------------------------------------------------------------------------SYSCONGLOMERATES |1 SYSTABLES |1 SYSCOLUMNS |1 SYSSCHEMAS |1 SYSCONSTRAINTS |1 SYSKEYS |1 SYSDEPENDS |1 SYSALIASES |1 SYSVIEWS |1 SYSCHECKS |1 SYSFOREIGNKEYS |1 SYSSTATEMENTS |1 SYSFILES |1 SYSTRIGGERS |1 SYSSTATISTICS |1 ij> -- test that a unique key can be explicitly not nullablecreate table pos1(c1 int not null unique);0 rows inserted/updated/deletedij> drop table pos1;0 rows inserted/updated/deletedij> create table pos1(c1 int not null, c2 int, constraint asdf unique(c1));ERROR X0Y32: Constraint 'ASDF' already exists in Schema 'APP'.ij> create table t1 (c1 int, c2 int, c3 int not null);0 rows inserted/updated/deletedij> create unique index i11 on t1 (c3);0 rows inserted/updated/deletedij> create unique index i12 on t1 (c1, c3 desc);0 rows inserted/updated/deletedij> insert into t1 values (1,2,3);1 row inserted/updated/deletedij> insert into t1 values (null, 4,5);1 row inserted/updated/deletedij> create table t2 (c1 int, c2 int, c3 int);0 rows inserted/updated/deletedij> insert into t2 values (1,2,3), (null, 4,5), (7,8,9);3 rows inserted/updated/deletedij> create unique index i21 on t2 (c3);0 rows inserted/updated/deletedij> create unique index i22 on t2 (c1, c3 desc);0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> -- bug 5520 - constraint names in new schemas.create table B5420_1.t1 (c1 int not null primary key);0 rows inserted/updated/deletedij> create table B5420_2.t2 (c2 int not null constraint c2pk primary key);0 rows inserted/updated/deletedij> -- two part constraint names are not allowedcreate table B5420_3.t3 (c3 int not null constraint B5420_3.c3pk primary key);0 rows inserted/updated/deletedij> create table B5420_4.t4 (c4 int not null, primary key (c4));0 rows inserted/updated/deletedij> create table B5420_5.t5 (c5 int not null, constraint c5pk primary key (c5));0 rows inserted/updated/deletedij> -- two part constraint names are not allowedcreate table B5420_6.t6 (c6 int not null, constraint B5420_6.c6pk primary key (c6));0 rows inserted/updated/deletedij> SELECT CAST (S.SCHEMANAME AS VARCHAR(12)), CAST (C.CONSTRAINTNAME AS VARCHAR(36)), CAST (T.TABLENAME AS VARCHAR(12)) FROM SYS.SYSCONSTRAINTS C , SYS.SYSTABLES T, SYS.SYSSCHEMAS SWHERE C.SCHEMAID = S.SCHEMAID AND C.TABLEID = T.TABLEID AND T.SCHEMAID = S.SCHEMAIDAND S.SCHEMANAME LIKE 'B5420_%' ORDER BY 1,2,3;1 |2 |3 --------------------------------------------------------------B5420_1 |xxxxGENERATED-IDxxxx |T1 B5420_2 |C2PK |T2 B5420_3 |C3PK |T3 B5420_4 |xxxxGENERATED-IDxxxx |T4 B5420_5 |C5PK |T5 B5420_6 |C6PK |T6 ij> -- clean updrop table B5420_1.t1;0 rows inserted/updated/deletedij> drop table B5420_2.t2;0 rows inserted/updated/deletedij> drop table B5420_4.t4;0 rows inserted/updated/deletedij> drop table B5420_5.t5;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -