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-pg...@hjp.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 黄宁 <huangning0...@gmail.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. > |_|_) | | > | | | h...@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
