{"record":{"id":"fc586fafe31de37f","repo":"phalcon/cphalcon","slug":"adding-a-foreign-key-constraint-to-an-existing-tab","errorCode":null,"errorMessage":"Adding a foreign key constraint to an existing table is not supported by SQLite","messagePattern":"Adding a foreign key constraint to an existing table is not supported by SQLite","errorType":"exception","errorClass":"SqliteAlterForeignKeyNotSupported","httpStatus":null,"severity":"error","filePath":"phalcon/Db/Dialect/Sqlite.zep","lineNumber":101,"sourceCode":"\n        return sql;\n    }\n\n    /**\n     * SQLite cannot ALTER an existing table to add a CHECK constraint;\n     * the constraint must be declared at CREATE TABLE time.\n     */\n    public function addCheck( string tableName,  string schemaName, <CheckInterface> check) -> string\n    {\n        throw new SqliteAlterCheckNotSupported();\n    }\n\n    /**\n     * Generates SQL to add an index to a table\n     */\n    public function addForeignKey( string tableName,  string schemaName, <ReferenceInterface> reference) -> string\n    {\n        throw new SqliteAlterForeignKeyNotSupported();\n    }\n\n    /**\n     * Generates SQL to add an index to a table\n     */\n    public function addIndex( string tableName,  string schemaName, <IndexInterface> index) -> string\n    {\n        var indexType;\n        string sql;\n\n        let indexType = index->getType();\n\n        if !empty indexType {\n            let sql = \"CREATE \" . indexType . \" INDEX \";\n        } else {\n            let sql = \"CREATE INDEX \";\n        }\n","sourceCodeStart":83,"sourceCodeEnd":119,"githubUrl":"https://github.com/phalcon/cphalcon/blob/b7419de9cd0a8a3f48441ead84c9f8415d463e25/phalcon/Db/Dialect/Sqlite.zep#L83-L119","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Declare the foreign key in createTable() via the 'references' key: new Reference('fk_parts_robot', ['columns' => ['robot_id'], 'referencedTable' => 'robots', 'referencedColumns' => ['id']])","For an existing table, rebuild it: create a new table with the reference, copy rows, drop old, rename","Gate the call per dialect: if (!($adapter->getDialect() instanceof Sqlite)) { $adapter->addForeignKey(...); }"],"exampleFix":"// before\n$connection->addForeignKey('parts', null,\n    new Reference('fk_robot', ['columns' => ['robot_id'], 'referencedTable' => 'robots', 'referencedColumns' => ['id']])\n);\n\n// after — inline at CREATE TABLE on SQLite\n$connection->createTable('parts', null, [\n    'columns' => [new Column('robot_id', ['type' => Column::TYPE_INTEGER])],\n    'references' => [\n        new Reference('fk_robot', ['columns' => ['robot_id'], 'referencedTable' => 'robots', 'referencedColumns' => ['id']]),\n    ],\n]);","handlingStrategy":"type-guard","validationCode":"if ($adapter->getDialect() instanceof \\Phalcon\\Db\\Dialect\\Sqlite) {\n    throw new RuntimeException('SQLite: declare foreign keys in createTable(), not addForeignKey()');\n}\n$adapter->addForeignKey($table, $schema, $reference);","typeGuard":"function supportsAddForeignKey(\\Phalcon\\Db\\Adapter\\AdapterInterface $adapter): bool\n{\n    return !($adapter->getDialect() instanceof \\Phalcon\\Db\\Dialect\\Sqlite);\n}","tryCatchPattern":"try {\n    $adapter->addForeignKey($table, $schema, $reference);\n} catch (\\Phalcon\\Db\\Exceptions\\SqliteAlterForeignKeyNotSupported $e) {\n    rebuildSqliteTableWithReferences($adapter, $table, [$reference]);\n}","preventionTips":["Put references in the createTable() definition for SQLite-compatible migrations","Write migrations as declarative full-table definitions instead of incremental ALTERs when multi-backend","Enable PRAGMA foreign_keys = ON for enforcement; the constraint itself must exist at CREATE time"],"tags":["phalcon","sqlite","ddl","foreign-key","alter-table"],"backgroundTag":"alter-table-unsupported","analyzedSha":"b7419de9cd0a8a3f48441ead84c9f8415d463e25","analyzedAt":"2026-08-21T06:21:18.811Z","schemaVersion":2},"datasetVersion":"2026-08-21T11:28:35.574Z"}