Magento – How to add foreign key in db_schema.xml

database-schemadeclarative-schemamagento2.3upgradeschema

I'm using magento 2.3 to create a custom module, i tried to create database tables for my custom module in etc/db_schema.xml like this:

<table comment="province Table" engine="innodb" name="province" resource="default">
    <column comment="Entity Id" identity="true" name="province_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
    <constraint referenceId="PRIMARY" xsi:type="primary">
        <column name="province_id"/>
    </constraint>
    <column length="255" name="name" nullable="true" xsi:type="varchar"/>
</table>
<table comment="City Table" engine="innodb" name="city" resource="default">
    <column comment="Entity Id" identity="true" name="city_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
    <constraint referenceId="PRIMARY" xsi:type="primary">
        <column name="city_id"/>
    </constraint>
    <column name="province_id" nullable="true" xsi:type="integer"/>
    <column length="255" name="name" nullable="true" xsi:type="varchar"/>
    <column length="255" name="type" nullable="true" xsi:type="varchar"/>
</table>

There are two new tables province and city. Here province_id field is the primary key of province table. I also put the province_id field in city table, but for now, that field does not refer as a foreign key to province table, how can I make the province_id in city table a foreign key that refers to province table province_id?

Best Answer

here is solution of your problem

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table comment="province Table" engine="innodb" name="province" resource="default">
        <column comment="Entity Id" identity="true" name="province_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
        <column length="255" name="name" nullable="true" xsi:type="varchar"/>

        <constraint referenceId="PRIMARY" xsi:type="primary">
            <column name="province_id"/>
        </constraint>
    </table>

    <table comment="City Table" engine="innodb" name="city" resource="default">
        <column comment="Entity Id" identity="true" name="city_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
        <column comment="Province id" identity="false" name="province_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
        <column length="255" name="name" nullable="true" xsi:type="varchar"/>
        <column length="255" name="type" nullable="true" xsi:type="varchar"/>

        <constraint referenceId="PRIMARY" xsi:type="primary">
            <column name="city_id"/>
        </constraint>

        <constraint xsi:type="foreign" referenceId="PROVINCE_PROVINCE_ID_CITY_PROVINCE_ID" table="city" column="province_id" referenceTable="province" referenceColumn="province_id"/>
    </table>
</schema>
Related Topic