默认规则
DB2主健对应索引的命名规则,DB2数据库中,如果建表时指定一列为主健,那么DB2会自动在这一列上创建一个索引,模式名为SYSIBM,索引命名规则为 SQLyymmddhhmmssxxx,即当前时间戳+编号。
$ db2levelDB21085I 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.