Edit existing FOREIGN KEY constraints in PostgreSQL with the ALTER TABLE command.

While setting up a test database on my system, I discovered the need to ALTER existing FOREIGN KEY‘s. Here’s what I studied, learned, and implemented.


Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.3 LTS (Xenial Xerus) and PostgreSQL 10.2 for these exercises.

richard-payette-522432-unsplash

Photo by Richard Payette on Unsplash

Steps

We will follow this order to update the FOREIGN KEY‘s.

  1. Use ALTER TABLE command to drop any existing FOREIGN KEY‘s.
  2. Use ALTER TABLE command to add the needed FOREIGN KEY‘s back to the table.
  3. Verify new keys are in place and updated.

Current Structure.

With the below table structure, we can see three FOREIGN KEY constraints.

fab_tracking=> \d pipe_kind;
               Table "public.pipe_kind"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 k_pipe_id | integer |           |          |
 k_kind_id | integer |           |          |
Foreign-key constraints:
    "k_pipe_fk" FOREIGN KEY (k_pipe_id) REFERENCES pipe(pipe_id)
    "kind_fk" FOREIGN KEY (k_kind_id) REFERENCES kind_type(kind_id)
    "pipe_kind_k_pipe_id_fkey" FOREIGN KEY (k_pipe_id) REFERENCES pipe(pipe_id)

Yet, I need to change the definitions and specify an ON UPDATE CASCADE ON DELETE CASCADE ‘contract’ for each constraint.
How can I do that?

After reading this informative blog post, I decided to use the demonstrated examples there, and apply them to my own needs.

Implementing the Changes.

All changes are built upon the ALTER TABLE command.
But, in order to make them, we need to DROP, those existing keys first:

fab_tracking=> ALTER TABLE pipe_kind DROP CONSTRAINT k_pipe_fk;
ALTER TABLE

Next, we can reuse that same FOREIGN KEY constraint name again, in a follow-up ALTER TABLE command. This time, specifying those ON DELETE CASCADE ON UPDATE CASCADE options we need.

fab_tracking=> ALTER TABLE pipe_kind ADD CONSTRAINT k_pipe_fk FOREIGN KEY (k_pipe_id) REFERENCES pipe(pipe_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE

Easily enough, we just repeat the same process for the two remaining FOREIGN KEY‘s.

fab_tracking=> ALTER TABLE pipe_kind DROP CONSTRAINT kind_fk;
ALTER TABLE

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

fab_tracking=> ALTER TABLE pipe_kind ADD CONSTRAINT kind_fk FOREIGN KEY (k_kind_id) REFERENCES kind_type(kind_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE
fab_tracking=> ALTER TABLE pipe_kind DROP CONSTRAINT pipe_kind_k_pipe_id_fkey;
ALTER TABLE
fab_tracking=> ALTER TABLE pipe_kind ADD CONSTRAINT pipe_kind_k_pipe_id_fkey FOREIGN KEY(k_pipe_id) REFERENCES pipe(pipe_id) ON DELETE CASCADE ON UPDATE ;
ERROR: syntax error at or near ";"
LINE 1: ...e_id) references pipe(pipe_id) on delete cascade on update ;
^

(Ooops… Left out the CASCADE keyword for that one. Let’s try that again.)

fab_tracking=> ALTER TABLE pipe_kind ADD CONSTRAINT pipe_kind_k_pipe_id_fkey FOREIGN KEY(k_pipe_id) REFERENCES pipe(pipe_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE

That’s better.

Here’s the final description of the pipe_kind table with all altered FOREIGN KEY constraints in place.

1
2
3
4
5
6
7
8
9
10
fab_tracking=> \d pipe_kind;
               Table "public.pipe_kind"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 k_pipe_id | integer |           |          |
 k_kind_id | integer |           |          |
Foreign-key constraints:
    "k_pipe_fk" FOREIGN KEY (k_pipe_id) REFERENCES pipe(pipe_id) ON UPDATE CASCADE ON DELETE CASCADE
    "kind_fk" FOREIGN KEY (k_kind_id) REFERENCES kind_type(kind_id) ON UPDATE CASCADE ON DELETE CASCADE
    "pipe_kind_k_pipe_id_fkey" FOREIGN KEY (k_pipe_id) REFERENCES pipe(pipe_id) ON UPDATE CASCADE ON DELETE CASCADE

Try Them Yourself

I hope through this blog post and the linked article, you can change existing FOREIGN KEY‘s in your tables as needed.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.