mySQL - Unable to Insert Values to Table

debugcn Published at Dev

Amran

I am trying to insert a data to my database table using sql queries. I receive this error when updating,

INSERT INTO `permohonan_cuti` (`permohonan_id`, `baki_cuti_permohonan`, `tarikh_mohon`, `tarikh_mula`, `tarikh_akhir`, `sokongan`, `pengganti`, `tujuan`, `kelulusan`, `pelulus`, `staff_id`, `cuti_id`, `katCuti_id`) 
VALUES 
(1603, 8, '2017-03-29 16:50:24', '2017-04-04', '0000-00-00', 'Dalam Proses', '39', 'HAL PERIBADI', 'Dalam Proses', NULL, 91, 88, 1),
(1604, 19, '2017-03-29 20:28:12', '2017-03-29', '0000-00-00', 'Dalam Proses', '132', 'DEMAM,BATUK,SELSEMA', 'Lulus', '378078', 141, 138, 5)

MySQL said: Documentation

#1452 - Cannot add or update a child row: a foreign key constraint fails (`ktmbcp_staff`.`permohonan_cuti`, CONSTRAINT `permohonan_cuti_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE ON UPDATE CASCADE)

May I know what cause the problem and how to fix it? Are my tables correct?

Below are the tables,

CREATE TABLE `permohonan_cuti` (
  `permohonan_id` int(11) NOT NULL,
  `baki_cuti_permohonan` float DEFAULT NULL,
  `tarikh_mohon` datetime NOT NULL,
  `tarikh_mula` date DEFAULT NULL,
  `tarikh_akhir` date DEFAULT NULL,
  `sokongan` varchar(50) DEFAULT NULL,
  `pengganti` varchar(50) DEFAULT NULL,
  `tujuan` varchar(100) NOT NULL,
  `kelulusan` varchar(50) DEFAULT NULL,
  `pelulus` varchar(10) DEFAULT NULL,
  `staff_id` int(11) NOT NULL,
  `cuti_id` int(11) NOT NULL,
  `katCuti_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `permohonan_cuti`
 ADD CONSTRAINT `permohonan_cuti_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`),
 ADD CONSTRAINT `permohonan_cuti_ibfk_2` FOREIGN KEY (`cuti_id`) REFERENCES `cuti` (`cuti_id`) ON DELETE CASCADE,
 ADD CONSTRAINT `permohonan_cuti_ibfk_3` FOREIGN KEY (`katCuti_id`) REFERENCES `kat_cuti` (`katCuti_id`);

ALTER TABLE `permohonan_cuti`
  ADD PRIMARY KEY (`permohonan_id`),
  ADD KEY `staff_id` (`staff_id`),
  ADD KEY `cuti_id` (`cuti_id`),
  ADD KEY `katCuti_id` (`katCuti_id`);

CREATE TABLE `staff` (
  `staff_id` int(11) NOT NULL,
  `staff_no` varchar(8) NOT NULL,
  `ic_no` varchar(20) NOT NULL,
  `nama` varchar(50) NOT NULL,
  `j_id` int(11) DEFAULT NULL,
  `tarikh_khidmat` date DEFAULT NULL,
  `gred_id` int(11) DEFAULT NULL,
  `l_id` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `no_tel` varchar(12) DEFAULT NULL,
  `no_hp` varchar(12) DEFAULT NULL,
  `alamat` varchar(100) DEFAULT NULL,
  `no_akaun` int(15) DEFAULT NULL,
  `no_kwsp` varchar(15) DEFAULT NULL,
  `password` varchar(10) NOT NULL,
  `nama_waris` varchar(50) DEFAULT NULL,
  `tel_waris` varchar(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `staff`
  ADD CONSTRAINT `staff_ibfk_3` FOREIGN KEY (`j_id`) REFERENCES `jabatan` (`j_id`),
  ADD CONSTRAINT `staff_ibfk_4` FOREIGN KEY (`gred_id`) REFERENCES `gred_pekerja` (`gred_id`);

ALTER TABLE `staff`
  ADD PRIMARY KEY (`staff_id`),
  ADD KEY `j_id` (`j_id`),
  ADD KEY `gred_id` (`gred_id`);

Niagaradad

The message is pretty clear, you are trying to add a staff_id that doesn't exist in the staff table.

Collected from the Internet

Please contact [email protected] to delete if infringement.