Primary key column id_kegiatan on the table that referenced as foreign key:
CREATE TABLE `Kegiatan` (
`id_kegiatan` int(11) NOT NULL AUTO_INCREMENT,
`id_program` int(11) NOT NULL,
`nama_kegiatan` varchar(15) NOT NULL,
PRIMARY KEY (`id_kegiatan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
Table that the column id_kegiatan I have tried to set into foreign key:
CREATE TABLE `Perjalanan_Dinas` (
`id_dinas` int(11) NOT NULL AUTO_INCREMENT,
`id_kegiatan` int(11) NOT NULL,
`id_satker` int(11) NOT NULL,
`nomor_surat_tugas` varchar(30) DEFAULT NULL,
`nomor_sp2d` varchar(20) DEFAULT NULL,
`tanggal_surat_tugas` date DEFAULT NULL,
`tanggal_mulai_dinas` date DEFAULT NULL,
`tanggal_selesai_dinas` date DEFAULT NULL,
`tujuan_dinas` text DEFAULT NULL,
PRIMARY KEY (`id_dinas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
Foreign key query:
ALTER TABLE Perjalanan_Dinas ADD CONSTRAINT FOREIGN_id_kegiatan FOREIGN KEY (`id_kegiatan`) REFERENCES `Kegiatan`(`id_kegiatan`) ON DELETE CASCADE ON UPDATE CASCADE;
An error occured like this:
#1025 - Error on rename of './database/#sql-50ec_548' to './database/Perjalanan_Dinas' (errno: 150 "Foreign key constraint is incorrectly formed")
I have added index to set the 'id' column on the table that was meant to be a foreign key column, ensured the data type to be the same, but still an error occured.
Version: 10.4.28-MariaDB (phpmyadmin)