2

I have these tables: station and water_types. The user may only have one station, and if the user wants to delete the station, the corresponding water_types must also be deleted. In a station, there might be multiple water_types.

create table
  public.water_type (
    id uuid not null default uuid_generate_v4 (),
    name text not null,
    price numeric(10, 2) not null,
    user_id uuid not null,
    constraint water_type_pkey primary key (id),
    constraint water_type_user_id_fkey foreign key (user_id) references auth.users (id) on update cascade on delete restrict
  ) tablespace pg_default;

create table
  public.station (
    id uuid not null default gen_random_uuid (),
    created_at timestamp with time zone null,
    user_id uuid not null default auth.uid (),
    ..other data...
    tel_no numeric null,
    delivery_mode text null,
    constraint station_pkey primary key (id),
    constraint station_user_id_key unique (user_id),
    constraint tation_user_id_fkey foreign key (user_id) references auth.users (id) on update cascade on delete cascade
  ) tablespace pg_default;

Based on the table, this is how I planned to implement this:

I planned to compare the user_id that the station has with the user_id from the water_types. And if it matches, it will delete all the water_types with the matching user_id.

Is there any another approach to this that is better or would this already suffice?

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
JS3
  • 125
  • 5

1 Answers1

3

You can add a foreign key constraint to water_types that will do what you want:

ALTER TABLE water_types
   ADD FOREIGN KEY (user_id) REFERENCES station (user_id)
   ON DELETE CASCADE;
Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90