Postgresql Primary Key CONSTRAINT Study 010 Tips
主键约束
主键约束就是指一个字段或者多个字段组成的组合是唯一的。
跟唯一性约束差别在于，这些字段需要设置为NOT NULL。
create table t01(
id int unique not null,
info text
);
等同于下面语句
create table t02(
id int primary key,
info text
);
主键约束也是可以多个字段组合，示例如下:
create table t03(
id int not null,
info text not null,
primary key(id,info)
);
执行后结果如下:
postgres=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | |
Indexes:
"t01_id_key" UNIQUE CONSTRAINT, btree (id)
postgres=# \d t02
Table "public.t02"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | |
Indexes:
"t02_pkey" PRIMARY KEY, btree (id)
postgres=# \d t03
Table "public.t03"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | not null |
Indexes:
"t03_pkey" PRIMARY KEY, btree (id, info)
postgres=#
postgres=# create table t05(
postgres(# id int not null,
postgres(# info text,
postgres(# primary key(id,info)
postgres(# );
CREATE TABLE
postgres=# create table t06(
postgres(# id int,
postgres(# info text,
postgres(# primary key(id,info)
postgres(# );
CREATE TABLE
postgres=# create table t07(
postgres(# id int null,
postgres(# info text null,
postgres(# primary key(id,info)
postgres(# );
CREATE TABLE
postgres=#
postgres=# create table t08(
postgres(# id int unique,
postgres(# info text null,
postgres(# primary key(id,info)
postgres(# );
CREATE TABLE
postgres=# create table t09(
postgres(# id int unique null,
postgres(# info text null,
postgres(# primary key(id,info)
postgres(# );
CREATE TABLE
postgres=# \d t08
Table "public.t08"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | not null |
Indexes:
"t08_pkey" PRIMARY KEY, btree (id, info)
"t08_id_key" UNIQUE CONSTRAINT, btree (id)
postgres=# \d t07
Table "public.t07"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | not null |
Indexes:
"t07_pkey" PRIMARY KEY, btree (id, info)
postgres=# \d t06
Table "public.t06"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | not null |
Indexes:
"t06_pkey" PRIMARY KEY, btree (id, info)
postgres=# \d t05
Table "public.t05"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | not null |
Indexes:
"t05_pkey" PRIMARY KEY, btree (id, info)
postgres=# \d t04
Table "public.t04"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | not null |
Indexes:
"t04_pkey" PRIMARY KEY, btree (id, info)
postgres=# \d t043
Did not find any relation named "t043".
postgres=# \d t03
Table "public.t03"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | not null |
Indexes:
"t03_pkey" PRIMARY KEY, btree (id, info)
postgres=#
另外一张表只有一个主键，但是可以多个非空唯一索引。
查看表结构
常用\d 表名来查看表的结构信息,也可以通过 pg_class 系统表来查看。
postgres=# \d t01
Table "public.t01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
info | text | | |
Indexes:
"t01_id_key" UNIQUE CONSTRAINT, btree (id)
postgres=# select * from pg_class where relname = 't01';
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence |
relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition |
relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+
---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+-
-------------+------------+--------+------------+--------------
t01 | 2200 | 57346 | 0 | 10 | 0 | 57344 | 0 | 0 | 0 | 0 | 57347 | t | f | p |
r | 2 | 0 | f | f | f | f | f | f | f | t | d | f |
69883 | 1 | | |
(1 row)
postgres=#
postgres=#
postgres=#
postgres=# \x
Expanded display is on.
postgres=# select * from pg_class where relname = 't01';
-[ RECORD 1 ]-------+------
relname | t01
relnamespace | 2200
reltype | 57346
reloftype | 0
relowner | 10
relam | 0
relfilenode | 57344
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 57347
| t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relfrozenxid | 69883
relminmxid | 1
relacl |
reloptions |
relpartbound |
postgres=#
Have a good work&life! 2020/09 via LinHong