表中序列正确的定义方式(表与序列的粘连配置)

在以数据库指定表方式导出表序列、结构和数据时，发生表结构和数据被导出，序列没有被导出，于是整理了一下说明了序列的正确创建方式

一、序列创建方法

1.serial primary key方式建立序列

testdb=# 

testdb=# create table t1(id serial primary key, name varchar(10));

CREATE TABLE

testdb=# insert into t1(name) values('David');

INSERT 0 1

testdb=# insert into t1(name) values('Peter');

INSERT 0 1

testdb=# 

testdb=# 

2.表和序列分别建立，序列附加到表

1).建立表 

testdb=# create table t2(id bigint, name varchar(10));

CREATE TABLE

2).建立索引

testdb=# create sequence t2_id_seq increment by 1 minvalue 1 no maxvalue start with 1; 

CREATE SEQUENCE

3).设置序列拥有者

testdb=# ALTER SEQUENCE t2_id_seq OWNED BY t2.id;

ALTER SEQUENCE

4).设置序列对应列默认值

testdb=# ALTER TABLE ONLY t2 ALTER COLUMN id SET DEFAULT nextval('t2_id_seq'::regclass);

ALTER TABLE

testdb=# 

5).设置ID列主键约束

testdb=# 

testdb=# ALTER TABLE ONLY t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (id);

ALTER TABLE

testdb=# 

6).插入数据

testdb=# insert into t2(name) values('Jean');

INSERT 0 1

testdb=# insert into t2(name) values('jesmean');

INSERT 0 1

testdb=#

testdb=# 

testdb=# 

testdb=# \d t1_id_seq

          Sequence "public.t1_id_seq"

    Column     |  Type   |        Value        

---------------+---------+---------------------

 sequence_name | name    | t1_id_seq

 last_value    | bigint  | 2

 start_value   | bigint  | 1

 increment_by  | bigint  | 1

 max_value     | bigint  | 9223372036854775807

 min_value     | bigint  | 1

 cache_value   | bigint  | 1

 log_cnt       | bigint  | 31

 is_cycled     | boolean | f

 is_called     | boolean | t

Owned by: public.t1.id

testdb=# 

testdb=# 

testdb=# \d t2_id_seq

          Sequence "public.t2_id_seq"

    Column     |  Type   |        Value        

---------------+---------+---------------------

 sequence_name | name    | t2_id_seq

 last_value    | bigint  | 2

 start_value   | bigint  | 1

 increment_by  | bigint  | 1

 max_value     | bigint  | 9223372036854775807

 min_value     | bigint  | 1

 cache_value   | bigint  | 1

 log_cnt       | bigint  | 31

 is_cycled     | boolean | f

 is_called     | boolean | t

Owned by: public.t2.id

testdb=# 

testdb=# 

testdb=#

testdb=# \q

二、验证序列是否粘连表

1.表导出时附带序列

1).导出指定表，看是否包含序列

[postgres@localhost ~]$ pg_dump -Fc -d testdb --table=t1 -f testdb_t1_Fc.dump

[postgres@localhost ~]$ pg_dump -Fc -d testdb --table=t2 -f testdb_t2_Fc.dump

[postgres@localhost ~]$

2).建立新数据库 

[postgres@localhost ~]$ 

[postgres@localhost ~]$ createdb tdb

[postgres@localhost ~]$

3).导入数据及表结构

[postgres@localhost ~]$

[postgres@localhost ~]$ pg_restore -d tdb testdb_t1_Fc.dump 

[postgres@localhost ~]$ pg_restore -d tdb testdb_t2_Fc.dump 

[postgres@localhost ~]$ 

[postgres@localhost ~]$

4).查看表结构 

[postgres@localhost ~]$ 

[postgres@localhost ~]$ psql tdb

psql (9.5.2)

Type "help" for help.

tdb=# 

tdb=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | t1   | table | postgres

 public | t2   | table | postgres

(2 rows)

tdb=# \d t1

                                Table "public.t1"

 Column |         Type          |                    Modifiers                    

--------+-----------------------+-------------------------------------------------

 id     | integer               | not null default nextval('t1_id_seq'::regclass)

 name   | character varying(10) | 

Indexes:

    "t1_pkey" PRIMARY KEY, btree (id)

tdb=# \d t2

                                Table "public.t2"

 Column |         Type          |                    Modifiers                    

--------+-----------------------+-------------------------------------------------

 id     | bigint                | not null default nextval('t2_id_seq'::regclass)

 name   | character varying(10) | 

Indexes:

    "t2_pkey" PRIMARY KEY, btree (id)

5).查看序列

tdb=# \d t1_id_seq

          Sequence "public.t1_id_seq"

    Column     |  Type   |        Value        

---------------+---------+---------------------

 sequence_name | name    | t1_id_seq

 last_value    | bigint  | 2

 start_value   | bigint  | 1

 increment_by  | bigint  | 1

 max_value     | bigint  | 9223372036854775807

 min_value     | bigint  | 1

 cache_value   | bigint  | 1

 log_cnt       | bigint  | 0

 is_cycled     | boolean | f

 is_called     | boolean | t

Owned by: public.t1.id

tdb=# \d t2_id_seq

          Sequence "public.t2_id_seq"

    Column     |  Type   |        Value        

---------------+---------+---------------------

 sequence_name | name    | t2_id_seq

 last_value    | bigint  | 2

 start_value   | bigint  | 1

 increment_by  | bigint  | 1

 max_value     | bigint  | 9223372036854775807

 min_value     | bigint  | 1

 cache_value   | bigint  | 1

 log_cnt       | bigint  | 0

 is_cycled     | boolean | f

 is_called     | boolean | t

Owned by: public.t2.id

tdb=#

6).插入数据 

tdb=# 

tdb=# insert into t1(name) values('chris');

INSERT 0 1

tdb=# insert into t2(name) values('LCH');

INSERT 0 1

tdb=# 

tdb=# 

tdb=# select * from t1;

 id | name  

----+-------

  1 | David

  2 | Peter

  3 | chris

(3 rows)

tdb=# select * from t2;

 id |  name   

----+---------

  1 | Jean

  2 | jesmean

  3 | LCH

(3 rows)

tdb=# 

tdb=# 

tdb=# 

tdb=# \d t1_id_seq 

          Sequence "public.t1_id_seq"

    Column     |  Type   |        Value        

---------------+---------+---------------------

 sequence_name | name    | t1_id_seq

 last_value    | bigint  | 3

 start_value   | bigint  | 1

 increment_by  | bigint  | 1

 max_value     | bigint  | 9223372036854775807

 min_value     | bigint  | 1

 cache_value   | bigint  | 1

 log_cnt       | bigint  | 32

 is_cycled     | boolean | f

 is_called     | boolean | t

Owned by: public.t1.id

tdb=# 

tdb=# 

tdb=# 

tdb=# \d t2_id_seq 

          Sequence "public.t2_id_seq"

    Column     |  Type   |        Value        

---------------+---------+---------------------

 sequence_name | name    | t2_id_seq

 last_value    | bigint  | 3

 start_value   | bigint  | 1

 increment_by  | bigint  | 1

 max_value     | bigint  | 9223372036854775807

 min_value     | bigint  | 1

 cache_value   | bigint  | 1

 log_cnt       | bigint  | 32

 is_cycled     | boolean | f

 is_called     | boolean | t

Owned by: public.t2.id

tdb=# 

tdb=# 

2.表删除时序列是否跟随删除

1).原数据库

testdb=# 

testdb=# drop table t1;

DROP TABLE

testdb=# 

testdb=# 

testdb=# drop table t2;

DROP TABLE

testdb=# 

testdb=# 

testdb=# \d t1_id_seq 

Did not find any relation named "t1_id_seq".

testdb=# 

testdb=# 

testdb=# \d t2_id_seq 

Did not find any relation named "t2_id_seq".

testdb=# 

testdb=# 

2).导入数据库

tdb=# 

tdb=# 

tdb=# drop table t1;

DROP TABLE

tdb=# 

tdb=# drop table t2;

DROP TABLE

tdb=# 

tdb=# 

tdb=# 

tdb=# \d t1_id_seq 

Did not find any relation named "t1_id_seq".

tdb=# 

tdb=# 

tdb=# \d t2_id_seq 

Did not find any relation named "t2_id_seq".

tdb=# 

tdb=#