How to create or drop a foreign key and add Index in the table in Magento 2?

Part 4: Last part to this series post of database operation, we are going to cover in this post How to create or drop a foreign key and add index in the table?

Create a foreign key and add an Index:

Below shared example adds the foreign key FL_ALLOWED_CUSTOMDATAID using constraint node.

Adds INDEX_NAME index to the custom_form table using index node.

db_schema.xml:

Generate db_schema_whitelist.json file:

When adding a foreign key to the table, remember to regenerate the db_schema_whitelist.json file so it contains the key too.

Here MM_Db is my module name. Please replace it with your Module name.

Run upgrade command:

NOTE: Foreign keys can only be added to tables when both tables were created using a declarative schema (db_schema.xml).

Drop a foreign key:

In the below shared example modified db_schema.xml to drop a foreign key. If you are removing from the same module you can just remove that constraint node from the file. If you are dropping by another module just redeclare same constraint in that module db_schema.xml and add new attribute in that node disabled=true

db_schema.xml:

Run upgrade command:

NOTE: You can not delete a foreign key until it exists in the db_schema_whitelist.json file so when you are creating foreign key generate db_schema_whitelist.json file too.

Leave a Reply

Your email address will not be published. Required fields are marked *