DB2主健对应索引的命名规则

最新推荐文章于 2022-08-26 10:47:57 发布

匿_名_用_户

最新推荐文章于 2022-08-26 10:47:57 发布

阅读量2k

点赞数

分类专栏： DB2 文章标签： DB2 主健索引命名

本文链接：https://blog.csdn.net/qingsong3333/article/details/61924182

版权

DB2 专栏收录该内容

181 篇文章 9 订阅

订阅专栏

默认规则

DB2数据库中，如果建表时指定一列为主健，那么DB2会自动在这一列上创建一个索引，模式名为SYSIBM，索引命名规则为 SQLyymmddhhmmssxxx，即当前时间戳+编号。

$ db2level
DB21085I  This instance or install (instance name, where applicable: "e105q5a") 
uses "64" bits and DB2 code release "SQL10055" with level identifier 
"0606010E". Informational tokens are "DB2 v10.5.0.5", "s141128", "IP23626", and Fix Pack 
"5". Product is installed at "/opt/IBM/db2/V10.5.5".

$ db2 "create table t1(id int not null primary key , name char(20))"     
DB20000I  The SQL command completed successfully.                                                                                               
                                                                        
$ db2 "select substr(INDSCHEMA,1,30) as INDSCHEMA, substr(indname,1,30) as index_name from syscat.indexes where TABNAME='T1'"                   
                                                                        
INDSCHEMA                      INDEX_NAME                               
------------------------------ ------------------------------           
SYSIBM                         SQL170313094504170                       
                                                                        
  1 record(s) selected.

自定义命名

如果想要自定义主健对应索引的名子，可以在建表的时候不指定主健，而是通过 ALTER TABLE ADD CONSTRAINT的办法来指定主健约束，则约束名即索引名。

$ db2 "create table t2(id int not null, name char(20))" 
DB20000I  The SQL command completed successfully.

$ db2 "ALTER TABLE t2  ADD CONSTRAINT prmrykyt2 PRIMARY KEY(id)"
DB20000I  The SQL command completed successfully.

$ db2 "select substr(INDSCHEMA,1,30) as INDSCHEMA, substr(indname,1,30) as index_name from syscat.indexes where TABNAME='T2'"

INDSCHEMA                      INDEX_NAME                    
------------------------------ ------------------------------
E105Q5A                        PRMRYKYT2                     

  1 record(s) selected.