0

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"

3

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.

  • It's work perfectly sir, is padding mean length? – sayou May 9 at 9:38
  • 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 at 13:51

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.