A foreign key constraint fails everytime I want to UPDATE data

Question

I am experiencing an error in the PHP file on the website I created. When I want to UPDATE data, the program won't update it and instead displays an error message. Even though I've created the foreign key and the database is already connected, I'm sure the mistake is in the two lines of code below.

/ jika form edit data yang dipilih
elseif ($_GET['form'] == 'edit') {
    if (isset($_GET['id'])) {
        // fungsi query untuk menampilkan data dari tabel transaksi
        $query = mysqli_query($mysqli, "SELECT a.id_transaksi,a.tgl_transaksi,a.barang,a.pekerja,a.pelanggan,a.jumlah_barang,a.status,
                                        b.id_barang ,b.nama_barang,
                                        c.id_pekerja,c.nama_pekerja,
                                        d.id_pelanggan,d.identitas,d.alamat,d.telepon as id_pelanggan
                                        FROM transaksi as a INNER JOIN barang as b INNER JOIN pekerja as c INNER JOIN pelanggan as d
                                        ON a.barang=b.id_barang AND a.pekerja=c.id_pekerja AND a.pelanggan=d.id_pelanggan
                                        WHERE a.id_transaksi='$_GET[id]'")
            or die('Ada kesalahan pada query tampil data ubah : ' . mysqli_error($mysqli));
        $data  = mysqli_fetch_assoc($query);

        $id_transaksi   = $data['id_transaksi'];

        $tanggal         = $data['tgl_transaksi'];
        $tgl             = explode('-', $tanggal);
        $tgl_transaksi   = implode('-', array_reverse($tgl));
        $id_pekerja      = $data['id_pekerja'];
        $id_barang       = $data['id_barang'];
        $jumlah_barang   = $data['jumlah_barang'];
        $id_pelanggan    = $data['id_pelanggan'];
        $nama_barang    = $data['nama_barang'];
        $nama_pekerja    = $data['nama_pekerja'];
        $identitas    = $data['identitas'];
        $alamat    = $data['alamat'];
        $status          = $data['status'];
    }
?>

And the second code:

elseif ($_GET['act'] == 'update') {
        if (isset($_POST['simpan'])) {
            // Tambahkan kode debug di sini untuk menampilkan semua data POST
            echo "<pre>";
            print_r($_POST);
            echo "</pre>";
            if (isset($_POST['id_transaksi'])) {
                // ambil data hasil submit dari form
                $id_transaksi = mysqli_real_escape_string($mysqli, trim($_POST['id_transaksi']));
                $tanggal = $_POST['tgl_transaksi'];
                $tgl = explode('-', $tanggal);
                $tgl_transaksi = $tgl[2] . "-" . $tgl[1] . "-" . $tgl[0];

                $id_pekerja = mysqli_real_escape_string($mysqli, trim($_POST['id_pekerja']));
                $id_barang = mysqli_real_escape_string($mysqli, trim($_POST['id_barang']));
                $jumlah_barang = mysqli_real_escape_string($mysqli, trim($_POST['jumlah_barang']));
                $id_pelanggan = mysqli_real_escape_string($mysqli, trim($_POST['id_pelanggan']));
                $status = mysqli_real_escape_string($mysqli, trim($_POST['status']));

                // Tambahkan debug untuk memeriksa apakah variabel ada dan tidak null
                echo "ID Transaksi: $id_transaksi, ID Pekerja: $id_pekerja, ID Barang: $id_barang, Jumlah Barang: $jumlah_barang, ID Pelanggan: $id_pelanggan, Status: $status";

                // perintah query untuk mengubah data pada tabel transaksi
                $query = mysqli_query($mysqli, "UPDATE transaksi SET tgl_transaksi    = '$tgl_transaksi',
                                                                     pekerja          = '$id_pekerja',
                                                                     barang           = '$id_barang',
                                                                     jumlah_barang    = '$jumlah_barang',
                                                                     pelanggan        = '$id_pelanggan',
                                                                     status           = '$status'
                                                               WHERE id_transaksi     = '$id_transaksi'")
                    or die('Ada kesalahan pada query update : ' . mysqli_error($mysqli));

                // cek query
                if ($query) {
                    // jika berhasil tampilkan pesan berhasil update data
                    header("location: ../../main.php?module=transaksi&alert=2");
                }
            }
        }
    }

Here is the error:

Fatal error: Uncaught mysqli_sql_exception: Cannot add or update a child row: a foreign key constraint fails (`global_survey`.`transaksi`, CONSTRAINT `FK_transaksi_barang` FOREIGN KEY (`barang`) REFERENCES `barang` (`id_barang`) ON DELETE CASCADE ON UPDATE CASCADE) in C:\xampp\htdocs\WebGlobalSurvey\admin\modules\transaksi\proses.php:61 Stack trace: #0 C:\xampp\htdocs\WebGlobalSurvey\admin\modules\transaksi\proses.php(61): mysqli_query(Object(mysqli), 'UPDATE transaks...') #1 {main} thrown in C:\xampp\htdocs\WebGlobalSurvey\admin\modules\transaksi\proses.php on line 61

I have tried to modify the INNER JOIN, because I suspect there is an error in that line of code, but the program still won't run properly. But if I update id_pekerja, id_pelanggan, and id_barang, it will work. And won't work if I only change one of these.

This is the problem: id_pekerja, id_pelanggan and id_barang are empty, I don't know why

Answer 1

Check Foreign Key Constraints: Verify that the foreign key constraints on the transaksi table, especially FK_transaksi_barang, are correctly set up. Ensure that the referenced columns in the barang table exist and are valid.

Data Integrity: Ensure that the values you are trying to update in the transaksi table (barang, id_pekerja, id_pelanggan) match the values in the referenced tables (barang, pekerja, pelanggan). If you are updating foreign keys, make sure the new values exist in the respective referenced tables.

Cascade Updates: If you are updating the primary key values (id_barang, id_pekerja, id_pelanggan) in the referenced tables, ensure that the foreign key relationships are set up to cascade updates. This means that when you update a referenced primary key, it should automatically update in the transaksi table as well.

Debugging: Use debugging techniques to check the values you are trying to update in your PHP code. You can add echo or var_dump statements to print out the values before the update query is executed. This will help you identify if there are any unexpected or incorrect values being passed.

SQL Statement: Double-check the SQL statement you are using for the update query. Ensure that the WHERE clause correctly identifies the row you intend to update.

Error Handling: Implement more robust error handling in your PHP code to capture and display detailed error messages. This will help you pinpoint the exact cause of the issue.

Testing: Try to update records one at a time and see if the issue persists. This will help you isolate whether the problem occurs with a specific combination of updates.