CODEWITHSUNDEEP
postgresql
KristiLuna
KristiLuna

Reputation: 1903

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

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

Upvotes: 1

Views: 4377

Answers (1)

Julius Tuskenis
Julius Tuskenis

Reputation: 1630

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'

Upvotes: 2

Related Questions