2

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 2 new tables which is province and city , 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 foreign key to province table, how can i make the province_id in city table as foreign key that refers to province table province_id ?

1

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>
1

Please refer Devdocs

<constraint xsi:type="foreign" name="COMPANY_CREDIT_COMPANY_ID_DIRECTORY_COUNTRY_COUNTRY_ID" table="company_credit" column="company_id" referenceTable="company" referenceColumn="entity_id" onDelete="CASCADE"/>
0

Try to use existing cms module as an example:

<constraint xsi:type="foreign" referenceId="CMS_BLOCK_STORE_STORE_ID_STORE_STORE_ID" table="cms_block_store"
                column="store_id" referenceTable="store" referenceColumn="store_id" onDelete="CASCADE"/>

Note: make sure both table column that you want to add as a key, have same property. Ex: Both column type, size etc. should same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.