Yii2迁移中如何检查指定名称的外键是否存在？

在Yii的数据库迁移中，为避免重复添加外键触发数据库报错，你可以通过查询对应数据库的系统表来判断目标外键是否已存在，不同数据库的查询逻辑略有区别：

MySQL/MariaDB 环境 #

直接查询information_schema.KEY_COLUMN_USAGE系统表，匹配外键名称和目标表：

$foreignKeyName = 'fk-product-user_id';
// 自动处理表前缀并去掉引号
$tableName = trim($this->db->quoteTableName('{{%product}}'), '`');

// 查询外键是否存在
$exists = $this->db->createCommand("
    SELECT COUNT(*)
    FROM information_schema.KEY_COLUMN_USAGE
    WHERE CONSTRAINT_NAME = :name
      AND TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = :table
")->bindValues([
    ':name' => $foreignKeyName,
    ':table' => $tableName,
])->queryScalar();

// 仅当外键不存在时执行添加操作
if (!$exists) {
    $this->addForeignKey('fk-product-user_id', '{{%product}}', 'user_id', '{{%user}}', 'id');
}

PostgreSQL 环境 #

针对PostgreSQL，需要查询information_schema.table_constraints表，同时过滤约束类型为外键：

$foreignKeyName = 'fk-product-user_id';
$tableName = trim($this->db->quoteTableName('{{%product}}'), '"');

$exists = $this->db->createCommand("
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE constraint_name = :name
      AND table_name = :table
      AND constraint_type = 'FOREIGN KEY'
")->bindValues([
    ':name' => $foreignKeyName,
    ':table' => $tableName,
])->queryScalar();

if (!$exists) {
    $this->addForeignKey('fk-product-user_id', '{{%product}}', 'user_id', '{{%user}}', 'id');
}

通用适配方案（支持多数据库） #

如果项目可能切换不同数据库，可以封装一个统一方法处理：

protected function foreignKeyExists($fkName, $table)
{
    $db = $this->db;
    // 适配不同数据库的表名引号格式
    $cleanTableName = trim($db->quoteTableName($table), $db->getQuoteCharacter());
    $driver = $db->driverName;

    $sql = match($driver) {
        'mysql' => "SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = :name AND TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table",
        'pgsql' => "SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_name = :name AND table_name = :table AND constraint_type = 'FOREIGN KEY'",
        'sqlite' => "SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND name = :name AND tbl_name = :table",
        default => "SELECT 0"
    };

    return $db->createCommand($sql)->bindValues([
        ':name' => $fkName,
        ':table' => $cleanTableName,
    ])->queryScalar() > 0;
}

// 使用示例
if (!$this->foreignKeyExists('fk-product-user_id', '{{%product}}')) {
    $this->addForeignKey('fk-product-user_id', '{{%product}}', 'user_id', '{{%user}}', 'id');
}

内容的提问来源于stack exchange，提问作者Anil Chaudhari