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@xxxxxxxxx> 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@xxxxxx | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
