Re: Delete a table automatic?

Hi,

I too agree with Adrian Klaver, the trigger is the only option

Thanks,

V Muralidharan
+91 9940302900

On Tue, 1 Nov 2022, 19:46 Peter J. Holzer, <hjp-pgsql(at)hjp(dot)at> wrote:

> 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!"
>