Get error foreign key violation

Question

I get problem while inserting data to database. It get an error like this:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (sipp-litbang1.atribut_proposal, CONSTRAINT fkAtributProposal FOREIGN KEY (id_pengguna) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE)array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL }

I make method to insert data, like this:

      public function pengajuan_proposal($pengguna, $judul_pro, $abstrak, $keywords, $lama_kegiatan, $bidang_fokus_id, $bidang_fokus_at, $topik_penelitian,
                                           $nidn, $nama_personil, $prodi, $jen_pend, $jab_fung, $danus1, $danus2, $danus3, $danin1, $danin2,
                                           $danin3){

            $this->conn->beginTransaction();
            try {


                $query = $this->conn->prepare("INSERT INTO identitas_proposal 
                                              (id_pengguna, judul_pro, abstrak, keywords, lama_kegiatan, bidang_fokus)
                                              VALUES (:id_pengguna, :judulpro, :abstrak, :keywords, :lamakegiatan, :bidangfokus)");
                $query->bindParam(':id_pengguna', $pengguna, PDO::PARAM_STR);
                $query->bindParam(':judulpro', $judul_pro, PDO::PARAM_STR);
                $query->bindParam(':abstrak', $abstrak, PDO::PARAM_STR);
                $query->bindParam(':keywords', $keywords, PDO::PARAM_STR);
                $query->bindParam(':lamakegiatan', $lama_kegiatan, PDO::PARAM_STR);
                $query->bindParam(':bidangfokus', $bidang_fokus_id, PDO::PARAM_STR);
                $query->execute();

                $lastInsertID = $this->conn->lastInsertId();

                $query = $this->conn->prepare("INSERT INTO atribut_proposal 
                                              (id_pengguna, bidang_fokusatribut, topik_penelitian)
                                              VALUES (:id_pengguna, :bidangfokusatribut, :topikpenelitian)");
                $query->bindParam(':id_pengguna', $lastInsertID, PDO::PARAM_STR);
                $query->bindParam(':bidangfokusatribut', $bidang_fokus_at, PDO::PARAM_STR);
                $query->bindParam(':topikpenelitian', $topik_penelitian, PDO::PARAM_STR);
                $query->execute();

                $query = $this->conn->prepare("INSERT INTO daftar_personil 
                                              (id_pengguna, nidn, nama_personil, program_studi, jenjang_pend, jabatan)
                                              VALUES (:id_pengguna, :nidn, :namapersonil, :programstudi, :jenjangpendidikan, :jabatan)");
                $query->bindParam(':id_pengguna', $lastInsertID, PDO::PARAM_STR);
                $query->bindParam(':nidn', $nidn, PDO::PARAM_STR);
                $query->bindParam(':namapersonil', $nama_personil, PDO::PARAM_STR);
                $query->bindParam(':programstudi', $prodi, PDO::PARAM_STR);
                $query->bindParam(':jenjangpendidikan', $jen_pend, PDO::PARAM_STR);
                $query->bindParam(':jabatan', $jab_fung, PDO::PARAM_STR);
                $query->execute();

                $query = $this->conn->prepare("INSERT INTO biaya_usulan 
                                              (id_pengguna, biaya_tahun1, biaya_tahun2, biaya_tahun3)
                                              VALUES (:id_pengguna, :biayatahun1, :biayatahun2, biayatahun3)");
                $query->bindParam(':id_pengguna', $lastInsertID, PDO::PARAM_STR);
                $query->bindParam(':biayatahun1', $danus1, PDO::PARAM_STR);
                $query->bindParam(':biayatahun2', $danus2, PDO::PARAM_STR);
                $query->bindParam(':biayatahun3', $danus3, PDO::PARAM_STR);
                $query->execute();

                $query = $this->conn->prepare("INSERT INTO biaya_internal 
                                              (id_pengguna, biaya_tahun1in, biaya_tahun2in, biaya_tahun3in)
                                              VALUES (:id_pengguna, :biayatahun1in, :biayatahun2in, biayatahun3in)");
                $query->bindParam(':id_pengguna', $lastInsertID, PDO::PARAM_STR);
                $query->bindParam(':biayatahun1in', $danin1, PDO::PARAM_STR);
                $query->bindParam(':biayatahun2in', $danin2, PDO::PARAM_STR);
                $query->bindParam(':biayatahun3in', $danin3, PDO::PARAM_STR);
                $query->execute();

                $this->conn->commit();
            }
            catch (PDOException $e){
                $this->conn->rollBack();
                echo $e->getMessage();
                var_dump($this->conn->errorInfo());
            }

            return true;
        }
    }

Then, on pengajuan.php file, I write codes like this:

<?php

    require_once __DIR__ . "/../class.proposal.php";

    $proposal = new PROPOSAL();
    if (isset($_POST['submit'])){
        $pengguna           = $_SESSION['userSession'];
        $judulpro           = $_POST['judulproposal'];
        $abstrak            = $_POST['abstrak'];
        $keywords           = $_POST['keywords'];
        $lama_kegiatan      = $_POST['lamakegiatan'];
        $bidang_fokus_id    = $_POST['bidangfokusid'];
        $bidang_fokus_at    = $_POST['bidangfokusat'];
        $topik_penelitian   = $_POST['topik'];
        $nidn               = $_POST['nidn'];
        $nama_personil      = $_POST['namapersonil'];
        $program_studi      = $_POST['programstudi'];
        $jenjang_pendidikan = $_POST['jenjangpendidikan'];
        $jabatan            = $_POST['jabatan'];
        $biaya_tahun1us     = $_POST['danus1'];
        $biaya_tahun2us     = $_POST['danus2'];
        $biaya_tahun3us     = $_POST['danus3'];
        $biaya_tahun1in     = $_POST['danin1'];
        $biaya_tahun2in     = $_POST['danin2'];
        $biaya_tahun3in     = $_POST['danin3'];


        try {
            if($proposal->pengajuan_proposal($pengguna, $judulpro, $abstrak, $keywords, $lama_kegiatan, $bidang_fokus_id, $bidang_fokus_at,
                $topik_penelitian, $nidn, $nama_personil, $program_studi, $jenjang_pendidikan,
                $jabatan, $biaya_tahun1us, $biaya_tahun2us, $biaya_tahun3us, $biaya_tahun1in,
                $biaya_tahun2in, $biaya_tahun3in)){


            }
        }
        catch (PDOException $e){
            echo $e->getMessage();
        }
    }
?>

I have tried to find solutions here and have implemented like delete parent tables (user, in my case), and create that table again or change default field id_pengguna into null, but it didn't work. What's the problem? Or maybe my codes still have errors or something else. Sorry for my broken english, by the way.

Answer 1

@VTaehyung, you are facing issue due to foreign key constraints. either "remove foreign key constraint" or ensure id_pengguna column value should be present in user table in id column.

we can't add a value in foreign table unless it present in primary table. for example , we can't add a employee in a department ,which is not exist yet.

For example, the Sales.SalesOrderHeader table has a foreign key link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. By creating this foreign key relationship, a value for SalesPersonID cannot be inserted into the SalesOrderHeader table if it does not already exist in the SalesPerson table.

references : https://learn.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-2017