EDIT: I tried an INSERT INTO from PhpMyAdmin. I noticed If I just do the GenreName and dont put an ID, the AutoIncrement is giving me a row ID of 2147483647 : INSERT INTO Genres( GenreName ) VALUES ('testFromPhpmyAdmin')
I got an error like this before when working directly in the MySQL database, but everything I tried from that past error on this new one is not working. I have exhausted as many StackOverflow posts about this as I could but none of the solutions ended up working. I have definitely checked that the ID is available in both of the tables. Also, it worked when I had the page simply have a form with text inputs for AlbumID and AlbumName, but I wanted to get rid of text entry for AlbumID as its autoincremented in the MySQL DB so users should not have to type/guess a new ID.
Error: SQL Error:
Errno: 1452
Error: Cannot add or update a child row: a foreign key constraint fails (andrew79_601.Albums, CONSTRAINT Albums_ibfk_2 FOREIGN KEY (ArtistID) REFERENCES Artists (ArtistID))
This happens when Im on on my addalbum.php page. This is the code I'm using: addalbum.php
<?php
//Get All genre
include 'dbconnect.php';
$sql_genre = "SELECT GenreID FROM Genres";
$genre_data = $mysqli->query($sql_genre);
// Get all artists
$sql_artist = "SELECT ArtistID FROM Artists";
$artists_data = $mysqli->query($sql_artist);
?>
<form action="addalbumssrv.php" method="post">
<!-- AlbumID:<input type="text" name="AlbumID" id="AlbumID"/></br> -->
Album Name:<input type="text" name="AlbumName" id="AlbumName"/></br>
<div>
<div class="dev-left">Genre ID: </div>
<div class="dev-left">
<?php if ($genre_data->num_rows > 0) { ?>
<select name="genre" style="width: 150px;">
<option value="">------Select-------</option>
<?php while ($row = $genre_data->fetch_assoc()) {
?>
<option value="<?php echo $row['ID']; ?>">
<?php echo $row['GenreID']; ?>
</option>
<?php }
?>
</select>
<?php
} else {
echo 'No Genre ID Found';
}
?>
</div>
</div>
<!--Artist drop down-->
<div>
<div class="dev-left">Artist ID: </div>
<div class="dev-left">
<?php if ($artists_data->num_rows > 0) { ?>
<select name="artist" style="width: 150px;">
<option value="">------Select-------</option>
<?php while ($row = $artists_data->fetch_assoc()) {
?>
<option value="<?php echo $row['ID']; ?>">
<?php echo $row['ArtistID']; ?>
</option>
<?php }
?>
</select>
<?php
} else {
echo 'No Artist ID Found';
}
?>
</div>
</div>
<input type="submit"/>
</form>
addalbumssrv.php:
<?php
//include 'dbconnect.php';
$link = new mysqli('127.0.0.1', 'andrew79_601', 'csis601', 'andrew79_601');
if ($link->connect_errno) {
echo "Error: Failed to make a MySQL connection, here is why: </br>";
echo "Errno: " . $link->connect_errno . "</br>";
echo "Error: " . $link->connect_error . "</br>";
exit;
}
// Escape user inputs for security
$AlbumID = mysqli_real_escape_string($link, $_REQUEST['AlbumID']);
$AlbumName = mysqli_real_escape_string($link, $_REQUEST['AlbumName']);
// attempt insert query execution
//$sql = "INSERT INTO Albums (AlbumID, AlbumName) VALUES ('$AlbumID', '$AlbumName')";
$sql = "INSERT INTO Albums (AlbumName) VALUES ('$AlbumName')";
if (!$result = $link->query($sql)) {
echo "Error: SQL Error: </br>";
echo "Errno: " . $link->errno . "</br>";
echo "Error: " . $link->error . "</br>";
exit;
}
?>
<script>
window.location='albums.php';
</script>
Table structure: https://i.sstatic.net/76JHg.jpg
The error message means that an attempt was made to insert or update a row in the "Albums" table, and the value supplied for the "ArtistID" column was invalid.
The foreign key constraint is enforcing a rule: the value in the "ArtistID" column must match a value in the "ArtistID" column of the "Artists" table.
artists
ArtistID artist_name
-------- -----------
112 Rush
albums
AlbumID ... ArtistID ...
------- --------
2112 112
If we attempt to insert a row to "Albums"
INSERT INTO Albums (..., ArtistID ,...) VALUES (..., 707 ,...)
MySQL is going to check that the value '707' supplied for the "ArtistID" column appears in the "Artists" table.
When MySQL (InnoDB) doesn't find a row in "Albums" with a matching value in the "ArtistID" column, it will throw a foreign key constraint error.
That's what MySQL is reporting: there isn't a matching row in "Artists".
To fix this, the "INSERT INTO Albums" statement must supply a valid value for "ArtistID".
If the column allows for NULL values, we could insert a NULL. Then the album would not be related to an artist.
Given the statement shown in the code:
INSERT INTO Albums (AlbumName) VALUES ('something')
The "ArtistID" column must be defined with a DEFAULT value other than NULL, or there's a BEFORE INSERT trigger that is assigning a non-NULL value.
The statement should probably be like this:
INSERT INTO Albums (AlbumName,ArtistID) VALUES ('something',112)
^^^^^^^^^ ^^^^
With a valid value supplied for the ArtistID column.