修改informix数据库表的主键,需要首先删除主键,再新建。
第一步:
查出该主键的约束的名字,查约束名SQL语句如下(TabName为表名):
SELECT constrname FROM sysconstraints
WHERE tabid = (SELECT tabid FROM systables
WHERE 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 TabName
ADD 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 "<ERROR> 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 "<ERROR>主键名查出为空!"
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数据库主键
最新推荐文章于 2024-09-10 15:57:14 发布