postgres怎么给表加主键和外键

A列是TableA的主键，B列为TableB的主键，B在TableA中作为外键

alter table TableA

add constraint FK_B foreign key (B)

reference TableB(B)

PostgreSQL中让主键自增长可先建立一个对应表的sequence

CREATE SEQUENCE test_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1

其中START是从数字几开始，INCREMENT BY是一次增长几个数字，NO MINVALUE是没有最小值，NO MAXVALUE是没有最大值；

然后修改表id字段

alter table test alter column id set default nextval('test_id_seq')

也可以在建表的时候使用：

"id" int4 DEFAULT nextval('t_ordercenter_info_history_id_seq'::regclass) NOT NULL

那oid在哪儿？到底为什么会出现这种情况 ？

来看看postgres官网对 oid的介绍：

根据stackoverflow的高票用户的回答：

*OIDs basically give you a built-in, globally unique id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they're non-standard), and their use is essentially deprecated :

In PostgreSQL 8.1 default_with_oids is off by defaultin prior versions of PostgreSQL, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

大意是你要是有个表没有用主键，这时候可以把oid充当为主键使用，当然这是没办法的办法。

总结： oid是给内部表做标识用的，不推荐使用。 建议将 default_with_oids 设置为off。 建表的时候，如果想使用主键，请自行建立。oid本身大小固定的，万一 行数超过了oid 的最大限制数（4 byte int），那就无法插入新行了。

---