一、create sequence owned by table.column
1、创建表及sequence
postgres=# create table test_sky (id int,name varchar(10));
CREATE TABLE
postgres=# create sequence seq_sky owned by test_sky.id;
CREATE SEQUENCE
2、查看sequence
postgres=# \ds
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | id_sec | sequence | postgres
public | seq_sky | sequence | postgres
public |
postgres=# \d seq_sky
Sequence "public.seq_sky"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.test_sky.id
3、drop table
postgres=# drop table test_sky;
DROP TABLE
postgres=# \ds
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | id_sec | sequence | postgres
public | seq_test1_id_seq | sequence | postgres
(2 rows)
可以看到创建的seq_sky被删除了。
二、创建表设置列默认值为nextval(sequence)
1、创建表及sequence
postgres=# create sequence seq_sky;
CREATE SEQUENCE
postgres=# create table test_sky (id int,name varchar(10));
CREATE TABLE
postgres=# ALTER TABLE test_sky
postgres-# ALTER COLUMN id SET DEFAULT nextval('seq_sky');
ALTER TABLE
postgres=#
postgres=# \d test_sky
Table "public.test_sky"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+------------------------------
id | integer | | | nextval('seq_sky'::regclass)
name | character varying(10) | | |
2、drop sequence
postgres=# drop sequence seq_sky;
ERROR: cannot drop sequence seq_sky because other objects depend on it
DETAIL: default value for column id of table test_sky depends on sequence seq_sky
HINT: Use DROP ... CASCADE to drop the dependent objects too.
由于sequence存在依赖,无法被删除
3、drop table/drop table cascade
postgres=# drop table test_sky;
DROP TABLE
postgres=# \ds
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | id_sec | sequence | postgres
public | seq_sky | sequence | postgres
public | seq_test1_id_seq | sequence | postgres
(3 rows)
postgres=# drop table test_sky cascade;
DROP TABLE
postgres=# \ds
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+----------
public | id_sec | sequence | postgres
public | seq_sky | sequence | postgres
public | seq_test1_id_seq | sequence | postgres
(3 rows)
可以看到均未删除sequence。
三、总结
综上所述,只有create sequence owned by table.column时,drop table会将涉及的sequence删除,否则仅是在列中引用,sequence不会被删除。
查阅官方文档进一步确认:
-
OWNED BY
table_name
.column_name
OWNED BY NONE
-
The
OWNED BY
option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. The specified table must have the same owner and be in the same schema as the sequence.OWNED BY NONE
, the default, specifies that there is no such association.
参考链接:
https://www.postgresql.org/docs/current/sql-createsequence.html