0

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"

1 Answer 1

0

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.

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.