Adding primary key to an existing custom table algoliasearch_queue_archive

Question

I am using Algolia extension in which algoliasearch_queue_archive is a table in which there isn't a primary key present.

I tried adding primary key to the table using below db_schema.xml code

<?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="algoliasearch_queue_archive">
        <column xsi:type="int" name="archive_id" unsigned="true" nullable="false" identity="true" comment="Archive ID"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="archive_id"/>
        </constraint>
    </table>
</schema>

Now when I run setup:upgrade command, I see error

Notice: Undefined index: PK_ALGOLIASEARCH_QUEUE_ARCHIVE in /var/www/html/myproject/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php on line 4083

Here is the schema of the table

CREATE TABLE `algoliasearch_queue_archive` (
  `pid` int unsigned DEFAULT NULL COMMENT 'PID',
  `class` varchar(50) NOT NULL COMMENT 'class',
  `method` text NOT NULL COMMENT 'Method',
  `data` text NOT NULL COMMENT 'Data',
  `error_log` text NOT NULL COMMENT 'Error Log',
  `data_size` int unsigned DEFAULT NULL COMMENT 'Data Size',
  `created_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Date and time of job creation'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='Algoliasearch Queue Archive Table'

Can someone guide, how to add primary key to this table?

Answer 1

I'm not sure if it is possible to add auto increment to existing third party column using db_schema.xml but I am able to add it using Schema Patch upgrade code shared below

<?php

namespace Vendor\Catalog\Setup\Patch\Schema;

use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\SchemaPatchInterface;
use Magento\Framework\Setup\Patch\PatchVersionInterface;
use Magento\Framework\DB\Ddl\Table;

class AlgoliaArchive implements SchemaPatchInterface, PatchVersionInterface
{
    /**
     * @var ModuleDataSetupInterface
     */
    private $moduleDataSetup;

    /**
     * AlgoliaArchive constructor
     *
     * @param ModuleDataSetupInterface $moduleDataSetup
     **/

    public function __construct(ModuleDataSetupInterface $moduleDataSetup)
    {
        $this->moduleDataSetup = $moduleDataSetup;
    }

    /**
     * Apply function
     *
     * @return SchemaPatchInterface|void
     * @throws LocalizedException
     * @throws \Zend_Validate_Exception
     */
    public function apply()
    {
        $this->moduleDataSetup->getConnection()->startSetup();

        $connection = $this->moduleDataSetup->getConnection();
        $tableName = $this->moduleDataSetup->getTable('algoliasearch_queue_archive');

        if ($connection->isTableExists($tableName)) {
            // Check if the archive_id column already exists
            $columns = $connection->describeTable($tableName);
            if (!isset($columns['archive_id'])) {
                // Add the archive_id column
                $connection->addColumn(
                    $tableName,
                    'archive_id',
                    [
                        'type' => Table::TYPE_INTEGER,
                        'nullable' => false,
                        'identity' => true,
                        'unsigned' => true,
                        'primary' => true,
                        'comment' => 'Archive ID'
                    ]
                );
            }
        }

        $this->moduleDataSetup->getConnection()->endSetup();

        return $this;
    }

    /**
     * @inheritdoc
     */
    public static function getDependencies()
    {
        return [];
    }

    /**
     * @inheritdoc
     */
    public function getAliases()
    {
        return [];
    }

    /**
     * @inheritdoc
     */
    public static function getVersion()
    {
        return '1.0.2';
    }
}

etc/module.xml code below

<?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="Vendor_Catalog"  setup_version="1.0.1">
        <sequence>
            <module name="Algolia_AlgoliaSearch"/>
        </sequence>
    </module>
</config>

Answer 2

Creating a Custom Module Create the module directory structure:

app/code/YourVendor/YourModule/

Create the registration.php file:

php

<?php
\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'YourVendor_YourModule',
    __DIR__
);

Create the module.xml file:

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="YourVendor_YourModule" setup_version="1.0.0"/>
</config>

Step 2: Create the Upgrade Script Magento 2 uses the InstallSchema and UpgradeSchema scripts to handle database schema changes.

Create the Setup directory:

app/code/YourVendor/YourModule/Setup/

Create or update the UpgradeSchema.php file:

php

<?php
namespace YourVendor\YourModule\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Ddl\Table;

class UpgradeSchema implements UpgradeSchemaInterface
{
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();

        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            $connection = $setup->getConnection();
            $tableName = $setup->getTable('algoliasearch_queue_archive');

            // Adding primary key to the table
            $connection->addIndex(
                $tableName,
                $setup->getIdxName($tableName, ['id'], \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_PRIMARY),
                ['id'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_PRIMARY
            );
        }

        $setup->endSetup();
    }
}

Step 3: Run the Upgrade Script After creating the UpgradeSchema.php script, run the following commands to apply the schema changes:

bin/magento setup:upgrade

Step 4: Verify the Changes Verify that the primary key has been added to the algoliasearch_queue_archive table by checking the database structure.

Example Verification Query You can run a query to check if the primary key is correctly set:

sql

SHOW KEYS FROM algoliasearch_queue_archive WHERE Key_name = 'PRIMARY';

Thank You!