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 ?

No its works, but I want to alter. I get all sort of errors "Syntax Error near ON" "near FOREIGN" ... Wasted an hour on this until giving up, can't help wondering what is the correct syntax to alter this thing. — Ben
– Ben, Commented Aug 13, 2017 at 13:44

Oto Shavadze · Accepted Answer · 2017-08-13 14:27:32Z

32

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;

edited Aug 13, 2017 at 14:27

answered Aug 13, 2017 at 14:19

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

Thank you, Looks like you are right. There seems no way to alter constraints options.

ojrac · Accepted Answer · 2019-05-10 13:22:45Z

4

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.

edited May 10, 2019 at 13:22

ojrac

13.5k6 gold badges40 silver badges40 bronze badges

answered Jul 24, 2018 at 15:45

acaruci

9299 silver badges14 bronze badges

1 Comment

Brice Joosten Over a year ago

Is it planned in future to be able to modify a constraint's actions without dropping it ? Still revelant problem nowadays. If I'm a bit distracted and forget to put CASCADE on both update and delete actions before filling everything else in the constraint, I kind of often forget it and end up having to do constraint all over again.

Collectives™ on Stack Overflow

Postgresql: Changing Action For Foreign Key Constraint

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related