Search code examples
phpsqlmysqlcomposite-key

Problem inserting row into table with composite key

I have a table (See image) set up that has a composite key of member_id and book_id. If I use phpMyAdmin SQL tab to insert a record that has member_id and book_id identical it inserts the record with no problem. However, using php to do the same it throws a fatal error of duplicate key. The record however is inserted. I have deleted the row from the table before attempting the php insert!

I understood that having a composite key where the two constituent parts are identical is fine. It's only when trying to insert two rows with the identical composite key that should be a problem?Screenshot of table

$sql = "INSERT INTO votes (member_id, book_id, votes, title) VALUES ('99', '99', '5', 'test')";

if (mysqli_query($link, $sql)) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>";
}

Fatal error: Uncaught mysqli_sql_exception: Duplicate entry '99-99' for key 'PRIMARY' in /home/whoo7078/g2webworks.co.uk/testVotesTable.php:22 Stack trace: #0 /home/whoo7078/g2webworks.co.uk/testVotesTable.php(22): mysqli_query() #1 {main} thrown in /home/whoo7078/g2webworks.co.uk/testVotesTable.php on line 22

Solution

  • It largely depends on the nature of your duplicate key of (member_id, book_id). If it is a primary key or a unique, that will allow you to insert/update a record as long as there is no other record in the database having the same values for each field inside the composite key.

    So, if you want to insert a (5, 6) or (6, 5) or (99, 99), it does not make a difference by itself. The difference is regarding the already existent records in the database. So, if you want to insert (5, 6), but there is already a record whose member_id is 5 and book_id is 6, then inserting a new record with the exact same member_id and book_id would violate the uniqueness of your composite key, which is not allowed in case of primary keys or unique keys. This is why you get the error you have mentioned and not because your member_id happened to be equal to your book_id in the record you attempted to insert. This is a general truth in keys that enforce uniqueness, like primary keys or unique keys.

    If your composite key is of different nature, like an index or something, then the problem is something else.

    Looking at your error message it is immediately clear that you had a primary key.