skip to Main Content

I have this error: "MySqlException: Cannot add or update a child row: a foreign_key constraint fails" on my C# project, when executing this code:

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;

But when I execute it on PhPMyAdmin it works perfectly.

Why does it fail on the aplication but works on PhPMyAdmin?

EDIT:
I’m using MySqlConnector/Mono

C# function that executes query:

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;
}

SHOW CREATE networks & users:

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

Complete console log:

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)

2

Answers


  1. The error "MySqlException: Cannot add or update a child row: a foreign_key constraint fails" typically indicates that there is a problem with a foreign key constraint in your database schema. This means that the networks table has a foreign key that references another table, and the value you’re trying to insert or update does not exist in the referenced table.
    So i would suggest you look at the other table and check if the value exists.
    Another reasons could be case sensitive issues or the connectivity of the database or database table.
    For more info check this site: https://sebhastian.com/mysql-error-1452/

    Login or Signup to reply.
  2. phpMyAdmin has got another rights.
    If your FOREIGN KEY is looking for a key (record) exists in another table (for example in table "B"), but your actual command is working in the table "A", I can imagine your user has no rights in table "B".
    I created some faults like this in mssql, but I think the logic is the same at the foreign keys in mysql.
    It is easy to check, I think it is worth a try.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search