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?

1
  • @LaurenzAlbe sorry, it was water_types
    – JS3
    Oct 31, 2023 at 15:38

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.