为PostgreSQL数据库中没有主键的表增加主键

概述PostgreSQL数据库测试环境中有多张表没有添加主键约束，只有一个serial的自增字段。现在需要把那些没有主键的表都加上，serial类型的字段为id 。 首先是怎么找到PostgreSQL数据库中哪些表没有主键？我们看下pg_class这个表，里面有个relhaspkey字段，如果为t说明有主键，f即没有主键。例如下面这个sql 。 SELECT n.nspname AS "Schema"

Postgresql数据库测试环境中有多张表没有添加主键约束，只有一个serial的自增字段。现在需要把那些没有主键的表都加上，serial类型的字段为ID 。

首先是怎么找到Postgresql数据库中哪些表没有主键？我们看下pg_class这个表，里面有个relhaspkey字段，如果为t说明有主键，f即没有主键。例如下面这个sql 。

SELECTn.nspnameAS"Schema",c.relnameAS"tablename",c.relhaspkeyAS"HasPK"FROMpg_catalog.pg_classcJOINpg_namespacenON(c.relnamespace=n.oIDANDn.nspnamenotin('information_schema','pg_catalog')ANDc.relkind='r')WHEREc.relhaspkey='f'ORDERBYc.relhaspkey,c.relname;

然后就是对这些表增加主键约束。删除和添加主键的sql如下所示：

altertableserverdropconstraintserver_pkey;altertableserveraddprimarykey(ID);

主键添加完成之后可以通过\d查看。

zhangnq=#\dservertable"public.server"Column|Type|ModifIErs--------+---------------+------------------------------------------------------ID|integer|notnulldefaultnextval('server_int_seq'::regclass)ip|character(50)|Indexes:"server_pkey"PRIMARYKEY,btree(ID)

最后就是把这个思路写到脚本里面，运行脚本批量添加。脚本里面把执行失败的表都放在error.log文件中。

脚本：

#!/bin/bashexportPATH=/opt/Postgresql/93/bin:$PATHexportPGDATA=/data/pgsqlexportPGHOME=/opt/Postgresql/93exportPGPORT=5432dbname=if[!$dbname];thenecho"Pleaseenterthedatabasename."exit1fipsql-c"\dt"-d$dbname>/dev/nullif[$?-ne0];thenexit1fierror_log="error.log"echo"">$error_logsql=`cat<<EOFSELECTn.nspnameAS"Schema",c.relnameAS"tablename"FROMpg_catalog.pg_classcJOINpg_namespacenON(c.relnamespace=n.oIDANDn.nspnamenotin('information_schema',c.relname;EOF`schemas=`psql-t-A-c"$sql"-d$dbname|cut-d"|"-f1`tables=`psql-t-A-c"$sql"-d$dbname|cut-d"|"-f1`forresin`psql-t-A-c"$sql"-d$dbname`doschema=`echo$res|cut-d"|"-f1`table=`echo$res|cut-d"|"-f2`tablename=`echo"$schema.$table"`psql-e-c"altertable$tablenameaddprimarykey(ID)"-d$dbnameif[$?-ne0];thenecho"$dbname:Addprimarykeyto$tablenameerror.">>$error_logfidone

说下碰到的的问题，在测试的时候发现如果把主键drop掉之后pg_class.relhaspkey值还是为t，但是用\d查看确实没有主键了。解决的办法是手动vacuum这个表，即vacuum server 。

zhangnq=#selectrelname,relhaspkeyfrompg_classwhererelname='server';relname|relhaspkey---------+------------server|t(1row)zhangnq=#altertableserverdropconstraintserver_pkey;ALTERtablezhangnq=#selectrelname,relhaspkeyfrompg_classwhererelname='server';relname|relhaspkey---------+------------server|t(1row)zhangnq=#vacuumserver;VACUUMzhangnq=#selectrelname,relhaspkeyfrompg_classwhererelname='server';relname|relhaspkey---------+------------server|f(1row)zhangnq=#altertableserveraddprimarykey(ID);ALTERtablezhangnq=#selectrelname,relhaspkeyfrompg_classwhererelname='server';relname|relhaspkey---------+------------server|t(1row)

查看pg_class的说明后发现原来pg_class只有在状态由false变成ture的时候会自动修改。这么设计可以提高并发性。

SeveraloftheBooleanflagsinpg_classaremaintainedlazily:theyareguaranteedtobetrueifthat'sthecorrectstate,butmaynotberesettofalseimmediatelywhentheconditionisnolongertrue.Forexample,relhasindexissetbyCREATEINDEX,butitisneverclearedbyDROPINDEX.Instead,VACUUMclearsrelhasindexifitfindsthetablehasnoindexes.ThisarrangementavoIDsraceconditionsandimprovesconcurrency.

参考链接：

http://www.postgresql.org/message-ID/1395116664140-5796526.post@n5.nabble.com

原文地址：http://www.sijitao.net/2026.HTML

总结

以上是内存溢出为你收集整理的为PostgreSQL数据库中没有主键的表增加主键全部内容，希望文章能够帮你解决为PostgreSQL数据库中没有主键的表增加主键所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。