表中序列正確的定義方式(表與序列的粘連配置)

在以數據庫指定表方式導出表序列、結構和數據時，發生表結構和數據被導出，序列沒有被導出，于是整理了一下說明了序列的正確創建方式

一、序列創建方法

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=#