Re: Delete a table automatic?

On 2022-11-01 07:41:14 -0600, Rob Sargent wrote:
> On 11/1/22 03:31, jian he wrote:
>
> On Tue, Nov 1, 2022 at 2:33 PM 黄宁 <huangning0722(at)gmail(dot)com> wrote:
>
> I now have two tables named A and B. Table B is calculated based on the
> data of table A. I wonder if table B can be automatically deleted when
> table A is deleted？
[...]
> you can use DROP TABLE CASCADE.
> DROP TABLE manual: https://www.postgresql.org/docs/current/
> sql-droptable.html
>
>
>
> Only If B has a foreign key reference to A

And even then it only drops the constraint, not the table (or the data):

hjp=> create table a (id serial primary key, t text);
CREATE TABLE
hjp=> create table b (id serial primary key, a int references a, t text);
CREATE TABLE
hjp=> \d a
Table "public.a"
╔════════╤═════════╤═══════════╤══════════╤═══════════════════════════════╗
║ Column │ Type │ Collation │ Nullable │ Default ║
╟────────┼─────────┼───────────┼──────────┼───────────────────────────────╢
║ id │ integer │ │ not null │ nextval('a_id_seq'::regclass) ║
║ t │ text │ │ │ ║
╚════════╧═════════╧═══════════╧══════════╧═══════════════════════════════╝
Indexes:
"a_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "b" CONSTRAINT "b_a_fkey" FOREIGN KEY (a) REFERENCES a(id)

hjp=> \d b
Table "public.b"
╔════════╤═════════╤═══════════╤══════════╤═══════════════════════════════╗
║ Column │ Type │ Collation │ Nullable │ Default ║
╟────────┼─────────┼───────────┼──────────┼───────────────────────────────╢
║ id │ integer │ │ not null │ nextval('b_id_seq'::regclass) ║
║ a │ integer │ │ │ ║
║ t │ text │ │ │ ║
╚════════╧═════════╧═══════════╧══════════╧═══════════════════════════════╝
Indexes:
"b_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"b_a_fkey" FOREIGN KEY (a) REFERENCES a(id)

[some inserts later]

hjp=> select * from b;
╔════╤═══╤══════╗
║ id │ a │ t ║
╟────┼───┼──────╢
║ 1 │ 1 │ foo1 ║
║ 2 │ 1 │ foo2 ║
║ 3 │ 2 │ bar1 ║
╚════╧═══╧══════╝
(3 rows)

hjp=> drop table a cascade;
NOTICE: drop cascades to constraint b_a_fkey on table b
DROP TABLE

hjp=> \d b
Table "public.b"
╔════════╤═════════╤═══════════╤══════════╤═══════════════════════════════╗
║ Column │ Type │ Collation │ Nullable │ Default ║
╟────────┼─────────┼───────────┼──────────┼───────────────────────────────╢
║ id │ integer │ │ not null │ nextval('b_id_seq'::regclass) ║
║ a │ integer │ │ │ ║
║ t │ text │ │ │ ║
╚════════╧═════════╧═══════════╧══════════╧═══════════════════════════════╝
Indexes:
"b_pkey" PRIMARY KEY, btree (id)

As you can see, the table is still there, but the foreign key constraint
is gone.

hjp=> select * from b;
╔════╤═══╤══════╗
║ id │ a │ t ║
╟────┼───┼──────╢
║ 1 │ 1 │ foo1 ║
║ 2 │ 1 │ foo2 ║
║ 3 │ 2 │ bar1 ║
╚════╧═══╧══════╝
(3 rows)

And the data in the table is also unchanged.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"