To change the data type for an existing column. You can create a new module and redefine the data type for it in db_schema.xml
file.
In your case, we'll change the data type for the attribute_id
column in the eav_attribute table from small_int
(has a maximum value signed is 32767) to int
(has a maximum value signed is 2147483647).
Assume the Vendor name is TuVan and the module name is ChangeEAVAtrributeTableColumnType, take the following steps:
Step 1: Create app/code/TuVan/ChangeEAVAtrributeTableColumnType/registration.php
<?php
use Magento\Framework\Component\ComponentRegistrar;
ComponentRegistrar::register(ComponentRegistrar::MODULE, 'TuVan_ChangeEAVAtrributeTableColumnType', __DIR__);
Step 2: Create app/code/TuVan/ChangeEAVAtrributeTableColumnType/etc/module.xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
<module name="TuVan_ChangeEAVAtrributeTableColumnType">
<sequence>
<module name="Magento_Eav"/>
</sequence>
</module>
</config>
Step 3: Create app/code/TuVan/ChangeEAVAtrributeTableColumnType/etc/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="eav_attribute">
<column xsi:type="int" name="attribute_id" unsigned="true" nullable="false" identity="true"
comment="Attribute ID"/>
</table>
</schema>
Step 4: Run the setup upgrade command to install and enable the module:
bin/magento setup:upgrade
Step 5: Run compilation code command:
bin/magento setup:di:compile
Step 6: Run deploy static content command:
bin/magento setup:static-content:deploy -f
You're done!
Since other tables also refer to the eav_attribute column, you'll need to find them and make the change for them as well.
You can run the following SQL query to get all references for a database column.
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = '<database>' AND
REFERENCED_TABLE_NAME = '<table>' AND
REFERENCED_COLUMN_NAME = '<column>';