Drop sequence and cascade

Question

I would like to drop the sequence used in table and the table itself in one statement using CASCADE, but I'm getting NOTICE and table is not dropped. For example:

CREATE SEQUENCE seq1; CREATE TABLE t1 (f1 INT NOT NULL DEFAULT nextval('seq1')); 

And then when I do:

DROP SEQUENCE seq1 CASCADE; 

I get following message, and the table is not dropped:

NOTICE:  drop cascades to default for table t1 column f1 

I'm definitely doing something wrong but these are my very first steps in PostgreSQL.

Answer 1

You have a misconception about dependencies. The table never is a depending object of an associated sequence and is never dropped by a

DROP SEQUENCE ... CASCADE; 

Only a DEFAULT value drawing from the sequence "depends" on the sequence and is set to NULL if the sequence is deleted with CASCADE.

It is the other way round: if the sequence is owned by a table column it is dropped with a

DROP TABLE f1 CASCADE; 

For a sequence to be owned by a table column you can either use the serial type as Milen already suggested. Or you can ALTER an existing sequence:

ALTER SEQUENCE seq1 OWNED BY t1.f1; 

Answer 2

I don't know why are you creating a sequence manually - maybe you have justification, or maybe it's due to habits working with another DBMS.

But if you don't have a special need for it, use the SERIAL pseudo-type and when you drop the table the sequence(s) behind the SERIAL column(s) will be dropped too.