Adding, listing and dropping foreign keys in MySQL
Created:02 Feb 2022 00:17:15 , in Web development
This time a couple of words on how to add, list and drop foreign keys with ON CASCADE DELETE clause. What are foreign keys for? You define your foreign keys on tables to ensure integrity of your data. One particular case of this is a foreign key with ON CASCADE DELETE clause. The clause ensures, that when an item from a referenced table is deleted, all the items related to it in the referencing table are deleted automatically.
Here is how this stuff looks in practice. Imagine, that in a database called sampleDatabase you have two tables, one called galleries (referenced table) and the other called uploads_galleries (referencing table). Table galleries has column called id, and uploads_galleries table has columnd called galleryId. You want to ensure, that if you delete a gallery with a particular id from galleries table all the items in column galleryId of uploads_galleries table, which are equal to the selected id also get deleted and it will be done automatically. To achieve this, add a foregin key on table galleries:
Adding a foreign key to referenced table
To add a foreign key to table galleries, switch to sampleDatabse and type:
ALTER TABLE uploads_galleries ADD FOREIGN KEY (galleryId) REFERNCES galleries(id) ON DELETE CASCADE;
Now you can delete items from gallery. Their ids in uploads_galleries table should get deleted too.
Listing foreign keys on referenced table
To list all the foreign keys on a referenced table galleries, type:
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'sampleDatabase' AND REFERENCED_TABLE_NAME = 'galleries';
You should get a table with the foreign key you added earlier.
Dropping a foreign key on referenced table
To drop a foreign key from a referenced table, you need the foreign key's name. You will find it in column called CONSTRAINT_NAME of the table obtained using code in the previous section. Assuming your constraint name is uploads_galleries_ibfk_1, you can drop the constrain like this:
ALTER TABLE galleries DROP FOREIGN KEY uploads_galleries_ibfk_1;
This is the minimum you need to know on how to manage you foreign keys in MySQL database. For more complex cases you might want to refer to MySQL documentation.
This post was updated on 04 Feb 2022 01:00:52
Author, Copyright and citation
Author of the this article - Sylwester Wojnowski - is a sWWW web developer. He has been writing computer code for the websites and web applications since 1998.
©Copyright, 2022 Sylwester Wojnowski. This article may not be reproduced or published as a whole or in parts without permission from the author. If you share it, please give author credit and do not remove embedded links.
Computer code, if present in the article, is excluded from the above and licensed under GPLv3.
Cite this article as:
Wojnowski, Sylwester. "Adding, listing and dropping foreign keys in MySQL." From sWWW - Code For The Web . https://wojnowski.net.pl//main/index/adding-listing-and-dropping-foreign-keys-in-mysql