应用执行MySQL插入更新报外键约束错误，PhPMyAdmin却正常？

错误现象 #

MySqlException: Cannot add or update a child row: a foreign_key constraint fails

执行特定INSERT...ON DUPLICATE KEY语句时触发上述错误，但在PhPMyAdmin中执行完全正常。

测试SQL语句 #

INSERT INTO networks (owner_name, name, password, ip, visible)
VALUES ('alex', 'alex_net', '1234', '192.168.1.1', 1)
ON DUPLICATE KEY
UPDATE name='alex_net', password = '1234', ip = '192.168.1.1', visible = 1;

C#执行代码（基于MySqlConnector/Mono） #

public static bool InsertOrUpdateNetwork(MySqlConnection connection, NetworkSO networkData)
{
    string sql =
        $"INSERT INTO networks (owner_name, name, password, ip, visible) " +
        $"VALUES ('{networkData.ownerName}', '{networkData.name}', '{networkData.password}', '{networkData.ip}', {networkData.visible}) " +
        $"ON DUPLICATE KEY " +
        $"UPDATE name='{networkData.name}', password='{networkData.password}', ip='{networkData.ip}', visible={networkData.visible}";

    MySqlCommand cmd = new MySqlCommand(sql, connection);
    int result = cmd.ExecuteNonQuery();

    return result > 0;
}

数据库表结构 #

CREATE TABLE networks (
 owner_name varchar(16) NOT NULL,
 name varchar(16) DEFAULT NULL,
 password varchar(16) DEFAULT NULL,
 ip varchar(15) NOT NULL,
 visible tinyint(4) NOT NULL,
 PRIMARY KEY (owner_name),
 UNIQUE KEY owner_name_UNIQUE (owner_name),
 CONSTRAINT fk_network_owner_name FOREIGN KEY (owner_name) REFERENCES users (name) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE users (
name varchar(16) NOT NULL, 
password varchar(16) DEFAULT NULL, 
logged tinyint(4) DEFAULT NULL, 
PRIMARY KEY (name), 
UNIQUE KEY name_UNIQUE (name) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

完整异常日志 #

MySqlException: Cannot add or update a child row: a foreign key constraint fails (sql7721749.networks, CONSTRAINT fk_network_owner_name FOREIGN KEY (owner_name) REFERENCES users (name) ON DELETE CASCADE ON UPDATE CASCADE)

临时解决方案 #

在主查询前后分别执行SET FOREIGN_KEY_CHECKS=0;和SET FOREIGN_KEY_CHECKS=1;，目前数据可正常插入更新，且删除用户时关联网络会被级联删除，符合预期。

待解答问题 #

• 为何同一SQL在C#代码中执行触发外键约束失败，在PhPMyAdmin中却正常？
• 上述临时解决方案是否合理、正确？

内容的提问来源于stack exchange，提问作者Alex Barra