{"record":{"id":"ae4b6e9da94312c6","repo":"phalcon/cphalcon","slug":"database-parameter-foreign-key-checks-has-to-be","errorCode":null,"errorMessage":"DATABASE PARAMETER 'FOREIGN_KEY_CHECKS' HAS TO BE 1","messagePattern":"DATABASE PARAMETER 'FOREIGN_KEY_CHECKS' HAS TO BE 1","errorType":"exception","errorClass":"MissingForeignKeyChecks","httpStatus":null,"severity":"error","filePath":"phalcon/Db/Adapter/Pdo/Mysql.zep","lineNumber":68,"sourceCode":"     */\n    protected type = \"mysql\";\n\n    /**\n     * Adds a foreign key to a table\n     */\n    public function addForeignKey(\n        string tableName, \n        string schemaName, \n        <ReferenceInterface> reference\n    ) -> bool {\n        var foreignKeyCheck;\n\n        let foreignKeyCheck = this->{\"prepare\"}(\n            this->dialect->getForeignKeyChecks()\n        );\n\n        if unlikely !foreignKeyCheck->execute() {\n            throw new MissingForeignKeyChecks();\n        }\n\n        return this->{\"execute\"}(\n            this->dialect->addForeignKey(\n                tableName,\n                schemaName,\n                reference\n            )\n        );\n    }\n\n    /**\n     * Returns an array of Phalcon\\Db\\Column objects describing a table\n     *\n     * ```php\n     * print_r(\n     *     $connection->describeColumns(\"posts\")\n     * );","sourceCodeStart":50,"sourceCodeEnd":86,"githubUrl":"https://github.com/phalcon/cphalcon/blob/b7419de9cd0a8a3f48441ead84c9f8415d463e25/phalcon/Db/Adapter/Pdo/Mysql.zep#L50-L86","documentation":"Thrown by Phalcon\\Db\\Adapter\\Pdo\\Mysql::addForeignKey(). Before emitting the ALTER TABLE ... ADD FOREIGN KEY statement, the adapter prepares and executes the dialect probe `SELECT @@foreign_key_checks` and requires the server to confirm FOREIGN_KEY_CHECKS is on (1). When that check does not succeed, the constraint is not added: MySQL accepts FK DDL while referential checking is suspended, which can silently produce an inconsistent schema. This is a connection/server state error, not a problem with the Reference definition itself.","triggerScenarios":"Calling $connection->addForeignKey($table, $schema, $reference) on a Pdo\\Mysql connection in the same session after `SET FOREIGN_KEY_CHECKS = 0` was issued (typical in migration/seed scripts that disable checks to drop or truncate tables); a server started with foreign_key_checks disabled globally; or the prepared probe failing to execute because the connection dropped or lost privileges.","commonSituations":"Migration scripts that toggle FOREIGN_KEY_CHECKS=0 for table teardown and never restore it; fixture loaders / DB testers disabling checks between tests; long-lived pooled connections reused after a dump import that began with SET FOREIGN_KEY_CHECKS=0; MySQL configured with foreign-key-checks=0 in my.cnf.","solutions":["Re-enable checks on the same connection, then retry: $connection->execute(\"SET FOREIGN_KEY_CHECKS = 1\");","Audit migrations/seeders for SET FOREIGN_KEY_CHECKS=0 and wrap the disable/enable pair in try/finally so the flag is always restored.","Verify state manually: SELECT @@foreign_key_checks; must return 1. If 0, also check SELECT @@GLOBAL.foreign_key_checks and server init settings.","If the probe statement itself fails, check connection health and that the session can read system variables."],"exampleFix":"// before\n$connection->execute(\"SET FOREIGN_KEY_CHECKS = 0\");\n// ... truncate/drop tables ...\n$connection->addForeignKey('orders', 'app', $reference); // throws MissingForeignKeyChecks\n\n// after\n$connection->execute(\"SET FOREIGN_KEY_CHECKS = 0\");\ntry {\n    // ... truncate/drop tables ...\n} finally {\n    $connection->execute(\"SET FOREIGN_KEY_CHECKS = 1\");\n}\n$connection->addForeignKey('orders', 'app', $reference); // ok","handlingStrategy":"validation","validationCode":"// Restore session state before DDL\n$ok = (int) $connection->query(\"SELECT @@foreign_key_checks\")->fetchColumn();\nif ($ok !== 1) {\n    $connection->execute(\"SET FOREIGN_KEY_CHECKS = 1\");\n}\n$connection->addForeignKey('orders', 'app', $reference);","typeGuard":null,"tryCatchPattern":"use Phalcon\\Db\\Exceptions\\MissingForeignKeyChecks;\n\ntry {\n    $connection->addForeignKey('orders', 'app', $reference);\n} catch (MissingForeignKeyChecks $e) {\n    // Session had FK checks off - restore and retry once\n    $connection->execute(\"SET FOREIGN_KEY_CHECKS = 1\");\n    $connection->addForeignKey('orders', 'app', $reference);\n}","preventionTips":["Never leave SET FOREIGN_KEY_CHECKS=0 unbalanced - always restore it in a finally block.","Run FK-adding migrations on a dedicated connection, not one shared with code that disables checks.","Add a pre-migration assertion that SELECT @@foreign_key_checks returns 1."],"tags":["mysql","foreign-keys","ddl","migration","phalcon-db"],"backgroundTag":"foreign-key-checks-disabled","analyzedSha":"b7419de9cd0a8a3f48441ead84c9f8415d463e25","analyzedAt":"2026-08-21T06:21:18.811Z","schemaVersion":2},"datasetVersion":"2026-08-21T11:28:35.574Z"}