Magento 2 - create a table with foreign key constraint

Question

I try to add this database table.

It has a foreign key constraint to the customer_entity table.

I need to configure it so if the customer is getting deleted that record is also getting deleted.

This is my attempt:

db_schema.xml

<?xml version="1.0" ?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="threepartment_debitpayment_vault" resource="default" engine="innodb" comment="threepartment_debitpayment_vault Table">
        <column name="vault_id" xsi:type="smallint" padding="6" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="vault_id"/>
        </constraint>

        <column name="customer_id" xsi:type="smallint" padding="6" unsigned="true" nullable="false" identity="false" comment="Customer ID"/>
        <constraint xsi:type="foreign"
                    referenceId="THREEPARTMENT_DEBITPAYMENT_VAULT_CSTR_ID_CSTR_ENTT_ENTT_ID"
                    table="threepartment_debitpayment_vault"
                    column="customer_id"
                    referenceTable="customer_entity"
                    referenceColumn="entity_id"
                    onDelete="CASCADE"
        />

        <column name="owner" nullable="true" xsi:type="varchar" comment="owner" length="255"/>
        <column name="iban" nullable="true" xsi:type="varchar" comment="iban" length="255"/>
        <column name="bic" nullable="true" xsi:type="varchar" comment="bic" length="255"/>
        <column name="bankname" nullable="true" xsi:type="varchar" comment="bankname" length="255"/>
    </table>
</schema>

However, I get this error if I execute setup:upgrade:

SQLSTATE[HY000]: General error: 1005 Can't create table `company_m2`.`threepartment_debitpayment_vault` (errno: 150 "Foreign key constraint is incorrectly formed"), query was: CREATE TABLE `threepartment_debitpayment_vault` (
`vault_id` smallint UNSIGNED NOT NULL  AUTO_INCREMENT COMMENT "Entity ID", 
`customer_id` smallint UNSIGNED NOT NULL   COMMENT "Customer ID", 
`owner` varchar(255) NULL  COMMENT "owner", 
`iban` varchar(255) NULL  COMMENT "iban", 
`bic` varchar(255) NULL  COMMENT "bic", 
`bankname` varchar(255) NULL  COMMENT "bankname", 
CONSTRAINT  PRIMARY KEY (`vault_id`), 
CONSTRAINT `THREEPARTMENT_DEBITPAYMENT_VAULT_CSTR_ID_CSTR_ENTT_ENTT_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`)  ON DELETE CASCADE
) ENGINE=innodb DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci COMMENT="threepartment_debitpayment_vault Table"

Answer 1

I figured it out. I had to change xsi:type="smallint" to xsi:type="int" for the column customer_id because it has to be exactly the same type as the referenced column customer_entity.entity_id.