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;

In fine

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

Tags:  mysql