修改informix数据库表的主键,需要首先删除主键,再新建。
修改informix数据库表的主键,需要首先删除主键,再新建
第一步:
查出该主键的约束的名字,查约束名SQL语句如下(TabName为表名):
SELECT constrname FROM sysconstraints WHERE tabid = (SELECT tabid FROM systablesWHERE tabname = ‘TabName’) and constrtype ='P' ;{constrtype ='P' 是主键约束,还有C 检查(Check)R 引用(外键)(Reference,Foreign Key) U 唯一 *** (Unique) N 非空(Not Null)}
第二步:
删除主键(TabName为表名,u144_58为上一步查出的约束名),SQL语句如下:
alter table TabName drop constraint u144_58;
第三步:
新建主键(主键为policyno,classcode,loandate),SQL语句如下:
>>
ALTER TABLE TabNameADD CONSTRAINT PRIMARY KEY (policyno,classcode,loandate)CONSTRAINT u144_58;{u144_58约束名可自定,必需唯一}
由于整个修改主键过程无法纯用SQL脚本完成,特写了一个SHELL脚本。
修改数据库主键 SHELL 脚本,需要informix的dbaccess支持:
#!/bin/sh##################################################功能: informix 修改数据库主键#################################################chg_primary_key(){ if [ $# -ne 2 ];then echo " Uargs: chg_primary_key tableName primaryKey n[$*]" exit 1 fi _tableName="$1" _primaryKey="$2" alias dbaccess='dbaccess' #查出老的主键约束名 _constrName=`dbaccess remotedb 2>> tmp.txt << EOF | xargs | cut -d ' ' -f2 select constrname from sysconstraints where constrtype='P' and tabid= ( select tabid from systables where tabname = '$_tableName' ) ; EOF ` if [ -z "$_constrName" ] || [ "$_constrName" = "constrname" ];then echo "主键名查出为空!" exit 1 fi #修改主键 dbaccess remotedb << EOF alter table $_tableName drop constraint $_constrName; alter table $_tableName add constraint primary key ($_primaryKey) constraint $_constrName;EOF}#debitrec修改主键为(policyno,classcode,loandate)chg_primary_key debitrec policyno,classcode,loandate#borrapp修改主键为(id,policyno,classcode,bdate)chg_primary_key borrapp id,policyno,classcode,bdate#quittask修改主键为(taskid,policyno,classcode)chg_primary_key quittask taskid,policyno,classcode
Informix如何更加有效的创建主键和外键