表中序列正确的定义方式(表与序列的粘连配置)
在以数据库指定表方式导出表序列、结构和数据时，发生表结构和数据被导出，序列没有被导出，于是整理了一下说明了序列的正确创建方式
一、序列创建方法
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=#