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.

Photo by Richard Payette on Unsplash
Steps
We will follow this order to update the FOREIGN KEY
‘s.
- Use
ALTER TABLE
command to drop any existingFOREIGN KEY
‘s. - Use
ALTER TABLE
command to add the neededFOREIGN KEY
‘s back to the table. - Verify new keys are in place and updated.
Current Structure.
With the below table structure, we can see three FOREIGN KEY
constraints.
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:
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.
ALTER TABLE
Easily enough, we just repeat the same process for the two remaining FOREIGN KEY
‘s.
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.
ALTER TABLE
ALTER TABLE
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.)
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.