Postgresql: Changing Action For Foreign Key Constraint

Question

I have a simple table like below.

create table chemlab.rule_header (
    id           serial PRIMARY KEY,
    name         varchar(50),
    grade        varchar(20),
    class_tag    varchar(20),    --tag added to sammple if match
    parent_id    int REFERENCES chemlab.rule_header(id) DEFAULT NULL,
    unique( grade, class_tag )
)

But afterwards, I found that I need to add ON DELETE action, the default is NO ACTION. I couldn't figure out how to change the action.

Now I have to DROP & ADD

ALTER table chemlab.rule_header 
    DROP CONSTRAINT rule_header_parent_id_fkey ;
ALTER TABLE rule_header  
    ADD CONSTRAINT rule_header_parent_id_fkey 
    FOREIGN KEY (parent_id) REFERENCES chemlab.rule_header(id) ON DELETE RESTRICT;

So what is the correct syntax to alter an action on foreign key constraint ?

Answer 1

Well, this not directly altering FOREIGN KEY constraint, and there are DROP and ADD still, though this is only one statement:

ALTER table  chemlab.rule_header 
    DROP CONSTRAINT rule_header_parent_id_fkey,
    ADD CONSTRAINT rule_header_parent_id_fkey 
    FOREIGN KEY (parent_id) REFERENCES chemlab.rule_header(id) ON DELETE RESTRICT;

Answer 2

Take a look at the documentation at https://www.postgresql.org/docs/current/sql-altertable.html. There are options to alter a few things about a constraint (like DEFERRABLE) but not for changing the action, as I understand you need.