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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<?xml version="1.0"?> <!-- /** * Copyright © Magento, Inc. All rights reserved. * See COPYING.txt for license details. */ --> <schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd"> <table name="custom_form" resource="default" engine="innodb" comment="creating custom form table"> <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID"/> <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name"/> <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email"/> <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message"/> <column xsi:type="timestamp" name="created_at" padding="10" comment="Created At" disabled="true"/> <constraint xsi:type="primary" referenceId="PRIMARY"> <column name="entity_id"/> </constraint> + <index referenceId="INDEX_NAME" indexType="btree"> <column name="name"/> </index> + <constraint xsi:type="foreign" referenceId="FL_ALLOWED_CUSTOMDATAID" table="declarative_table" column="entity_id" referenceTable="custom_data" referenceColumn="custom_data_id" onDelete="CASCADE"/> </table> <table name="custom_data" resource="default" engine="innodb" comment="creating custom data table"> <column xsi:type="smallint" name="custom_data_id" padding="6" unsigned="false" nullable="false" identity="true" comment="CUSTOM DATA ID"/> <column xsi:type="varchar" name="custom_name" nullable="false" length="25" comment="Name"/> <column xsi:type="varchar" name="custom_email" nullable="false" length="25" comment="Email"/> <column xsi:type="varchar" name="custom_message" nullable="false" length="255" comment="Message"/> <column xsi:type="timestamp" name="created_at" padding="10" comment="Created At" disabled="true"/> <constraint xsi:type="primary" referenceId="PRIMARY"> <column name="custom_data_id"/> </constraint> </table> </schema> |
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.
|
1 |
php bin/magento setup:db-declaration:generate-whitelist --module-name=MM_Db |
Here MM_Db is my module name. Please replace it with your Module name.
Run upgrade command:
|
1 |
php bin/magento setup:upgrade |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<?xml version="1.0"?> <!-- /** * Copyright © Magento, Inc. All rights reserved. * See COPYING.txt for license details. */ --> <schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd"> <table name="custom_form" resource="default" engine="innodb" comment="creating custom form table"> <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID"/> <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name"/> <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email"/> <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message"/> <column xsi:type="timestamp" name="created_at" padding="10" comment="Created At" disabled="true"/> <constraint xsi:type="primary" referenceId="PRIMARY"> <column name="entity_id"/> </constraint> <index referenceId="INDEX_NAME" indexType="btree"> <column name="name"/> </index> - <constraint xsi:type="foreign" referenceId="FL_ALLOWED_CUSTOMDATAID" table="declarative_table" column="entity_id" referenceTable="custom_data" referenceColumn="custom_data_id" onDelete="CASCADE"/> </table> <table name="custom_data" resource="default" engine="innodb" comment="creating custom data table"> <column xsi:type="smallint" name="custom_data_id" padding="6" unsigned="false" nullable="false" identity="true" comment="CUSTOM DATA ID"/> <column xsi:type="varchar" name="custom_name" nullable="false" length="25" comment="Name"/> <column xsi:type="varchar" name="custom_email" nullable="false" length="25" comment="Email"/> <column xsi:type="varchar" name="custom_message" nullable="false" length="255" comment="Message"/> <column xsi:type="timestamp" name="created_at" padding="10" comment="Created At" disabled="true"/> <constraint xsi:type="primary" referenceId="PRIMARY"> <column name="custom_data_id"/> </constraint> </table> </schema> |
Run upgrade command:
|
1 |
php bin/magento setup:upgrade |
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.