update or delete on table X violates foreign key constraint Y on table Z

Question

Hello I'm trying to run this: DELETE FROM ds_airflow.ab_view_menu WHERE name = 'DAG:vl2_daily'; (only once) however I'm seeing the below error:

ERROR:  update or delete on table "ab_view_menu" violates foreign key
 constraint "ab_permission_view_view_menu_id_fkey" on table
"ab_permission_view" DETAIL: Key (id)=(191) is still referenced from
 table "ab_permission_view".

I've tried this : delete from ds_airflow.ab_permission_view WHERE name = 'DAG:vl2_daily'; from stack overflow, seecomment on the very bottom, but I'm get this error:

ERROR:  column "name" does not exist
LINE 2: WHERE name = 'DAG:vl2_daily';

This is what ds_airflow.ab_permission_view table has:

s_airflow=> \d+ ds_airflow.ab_permission_view
            Table "ds_airflow.ab_permission_view"
    Column     |  Type   | Modifiers | Storage | Description 
---------------+---------+-----------+---------+-------------
 id            | integer | not null  | plain   | 
 permission_id | integer |           | plain   | 
 view_menu_id  | integer |           | plain   | 
Indexes:
    "ab_permission_view_pkey" PRIMARY KEY, btree (id)
    "ab_permission_view_permission_id_view_menu_id_key" UNIQUE, btree (permission_id, view_menu_id)
Foreign-key constraints:
    "ab_permission_view_permission_id_fkey" FOREIGN KEY (permission_id) REFERENCES ds_airflow.ab_permission(id)
    "ab_permission_view_view_menu_id_fkey" FOREIGN KEY (view_menu_id) REFERENCES ds_airflow.ab_view_menu(id)
Referenced by:
    TABLE "ds_airflow.ab_permission_view_role" CONSTRAINT "ab_permission_view_role_permission_view_id_fkey" FOREIGN KEY (permission_view_id) REFERENCES ds_airflow.ab_permission_view(id)
Has OIDs: no

Julius Tuskenis · Accepted Answer · 2021-05-14T07:20:29.207

1

You should either change "ab_permission_view_role_permission_view_id_fkey" to ON DELETE CASCADE, or delete associated records from "ds_airflow.ab_permission_view_role" like this:

DELETE FROM "ds_airflow.ab_permission_view"
USING ds_airflow.ab_view_menu
WHERE view_menu_id = ab_view_menu.id
  AND ab_view_menu.name = 'DAG:vl2_daily'

edited May 14 '21 at 07:20

answered May 13 '21 at 18:23

Julius Tuskenis

1,323
8
13

thanks for responding. I'm seeing this error when I try your above query: column "permission_view_id" does not exist LINE 1: ...rmission_view USING ds_airflow.ab_view_menu WHERE permission... – KristiLuna May 13 '21 at 19:02
Yes, my mistake, changed the answer to use (view_menu_id = ab_view_menu.id) condition. – Julius Tuskenis May 14 '21 at 07:21

update or delete on table X violates foreign key constraint Y on table Z

1 Answers1