Adding a new auto-increment field to the table `salesrule_coupon_usage` table returns a SQL error on setup:upgrade step

Question

In my custom module (tested on Magento CE 2.4.2-p1) feature of tracking the Sales Coupon usages sometimes have no info about Customer ID, and it is acceptable case. So I need to change table salesrule_coupon_usage: to remove table`s old complex primary key [coupon_id + customer_id ], add new identity column rule_usage_id and add a new primary key only with rule_usage_id column.

But I have a trouble with adding new identity column. I changed table via db_schema.xml in my custom module:

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="salesrule_coupon_usage">
    <!-- add custom column -->
    <column xsi:type="int" name="rule_usage_id" unsigned="true" nullable="true" identity="true" comment="Rule Usage ID"/>

    <!-- change complex PK key to own -->
    <constraint xsi:type="primary" referenceId="PRIMARY" disabled="true"></constraint>
    <constraint xsi:type="primary" referenceId="PRIMARY_COUPON_USAGE">
        <column name="rule_usage_id"/>
    </constraint>

    <!-- add new index (required because of identity type) -->
    <index referenceId="SALESRULE_COUPON_USAGE_RULE_USAGE_ID" indexType="btree">
        <column name="rule_usage_id"/>
    </index>
</table>

and on the ```setup:upgrade``` step I had this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'field list', query was: SELECT MAX(``) + 1 FROM `salesrule_coupon_usage`

If I remove identity="true" from new column - all changes would be successfully applied, but new column would not be auto-increment.

Maybe this is because salesrule_coupon_usage is an unusual table for Magento DB?

How to add a new identity column to it correctly?

Answer 1

try nullable="false"