phalcon/cphalcon · error · SqliteAlterForeignKeyNotSupported

Adding a foreign key constraint to an existing table is not

Error message

Adding a foreign key constraint to an existing table is not supported by SQLite

What it means

The SQLite dialect's addForeignKey() always throws SqliteAlterForeignKeyNotSupported. SQLite cannot add a FOREIGN KEY constraint to an existing table via ALTER TABLE; references must be declared inline in CREATE TABLE (with PRAGMA foreign_key_setup/foreign_keys handling enforcement). Throwing prevents migrations written for other backends from generating invalid SQLite SQL.

Source

Thrown at phalcon/Db/Dialect/Sqlite.zep:101

        return sql;
    }

    /**
     * SQLite cannot ALTER an existing table to add a CHECK constraint;
     * the constraint must be declared at CREATE TABLE time.
     */
    public function addCheck( string tableName,  string schemaName, <CheckInterface> check) -> string
    {
        throw new SqliteAlterCheckNotSupported();
    }

    /**
     * Generates SQL to add an index to a table
     */
    public function addForeignKey( string tableName,  string schemaName, <ReferenceInterface> reference) -> string
    {
        throw new SqliteAlterForeignKeyNotSupported();
    }

    /**
     * Generates SQL to add an index to a table
     */
    public function addIndex( string tableName,  string schemaName, <IndexInterface> index) -> string
    {
        var indexType;
        string sql;

        let indexType = index->getType();

        if !empty indexType {
            let sql = "CREATE " . indexType . " INDEX ";
        } else {
            let sql = "CREATE INDEX ";
        }

View on GitHub (pinned to b7419de9cd)

Solutions

1. Declare the foreign key in createTable() via the 'references' key: new Reference('fk_parts_robot', ['columns' => ['robot_id'], 'referencedTable' => 'robots', 'referencedColumns' => ['id']])
2. For an existing table, rebuild it: create a new table with the reference, copy rows, drop old, rename
3. Gate the call per dialect: if (!($adapter->getDialect() instanceof Sqlite)) { $adapter->addForeignKey(...); }

Example fix

// before
$connection->addForeignKey('parts', null,
    new Reference('fk_robot', ['columns' => ['robot_id'], 'referencedTable' => 'robots', 'referencedColumns' => ['id']])
);

// after — inline at CREATE TABLE on SQLite
$connection->createTable('parts', null, [
    'columns' => [new Column('robot_id', ['type' => Column::TYPE_INTEGER])],
    'references' => [
        new Reference('fk_robot', ['columns' => ['robot_id'], 'referencedTable' => 'robots', 'referencedColumns' => ['id']]),
    ],
]);

if ($adapter->getDialect() instanceof \Phalcon\Db\Dialect\Sqlite) {
    throw new RuntimeException('SQLite: declare foreign keys in createTable(), not addForeignKey()');
}
$adapter->addForeignKey($table, $schema, $reference);

function supportsAddForeignKey(\Phalcon\Db\Adapter\AdapterInterface $adapter): bool
{
    return !($adapter->getDialect() instanceof \Phalcon\Db\Dialect\Sqlite);
}

try {
    $adapter->addForeignKey($table, $schema, $reference);
} catch (\Phalcon\Db\Exceptions\SqliteAlterForeignKeyNotSupported $e) {
    rebuildSqliteTableWithReferences($adapter, $table, [$reference]);
}

When it happens

Trigger: Calling $sqliteAdapter->addForeignKey('parts', null, $reference); migration pipelines that add foreign keys in a separate pass after createTable(); cross-adapter migration scripts executed against SQLite.

Common situations: Running the same migration set on PostgreSQL in production and SQLite in tests; ORM-style schema versioning that applies constraint additions incrementally; CI using in-memory SQLite databases.

Related errors

• Adding a CHECK constraint to an existing table is not suppor
• Adding a primary key after table has been created is not sup
• Dropping a foreign key constraint is not supported by SQLite
• The table must contain at least one column
• The index 'columns' is required in the definition array

AI-assisted analysis of phalcon/cphalcon@b7419de9cd (2026-08-21). Data as JSON: /api/errors/fc586fafe31de37f. Report an issue: GitHub.