1

I try to add 2 foreign key to my table using db_schema.xml

<table name="test_table" engine="innodb" resource="default" >
    <column xsi:type="smallint" name="entity_id" identity="true" nullable="false" unsigned="false"/>
    <column xsi:type="smallint" name="id_test" unsigned="false" nullable="false" identity="false"/>
    <column xsi:type="smallint" name="id_user" unsigned="false" nullable="false" identity="false"/>
    <column xsi:type="varchar" name="title" length="255" unsigned="false" nullable="false" identity="false"/>
    <constraint xsi:type="primary" referenceId="PRIMARY">
        <column name="entity_id" />
    </constraint>

    <constraint xsi:type="foreign" referenceId="TEST_TEST_ID_" table="test_table" column="id_test" referenceTable="second_test_table" referenceColumn="entity_id" onDelete="CASCADE"/>
    <constraint xsi:type="foreign" referenceId="TEST_USER" table="test_table" column="id_user" referenceTable="admin_user" referenceColumn="user_id" onDelete="CASCADE"/>
</table>

but always I get this error: " (errno: 150 "Foreign key constraint is incorrectly formed" "

After a lot of try I found that the error is the user_id foreign key, and the error message is :

Column definition "id_user" and reference column definition "user_id" are different in tables "test_table" and "admin_user"

0

1 Answer 1

7

Change by following code:

<column xsi:type="int" name="id_user" padding="10" unsigned="true" nullable="true" identity="false"
            comment="User ID"/>

Column definition should be same if you use as a foreign key.

2
  • It's work perfectly sir, is padding mean length?
    – sayou
    May 9, 2019 at 9:38
  • 1
    I had the same issue, I've updated the definition of the FK to be the same with the PK, but now I get an error that the FK can't be created (most likely because of the existing data in the parent table). Is there a way to overcome this or do I need to empty the parent table?
    – Radu
    May 10, 2019 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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