cuirrently im using CodeIgniter-3.1.13 as my framework, and sqlite3 as my db driver i tried to delete a recor on my database using usual delte query builder, the problem is, the fopreign key contrain doesnt work, what i mean is, when i delete certain data on parrent table, the data on child table doesnt get deleted as well. but when i tried to delete a reord freom table parrent from db browser sql command, it worked
BEGIN TRANSACTION;
DROP TABLE IF EXISTS "attendance";
CREATE TABLE IF NOT EXISTS "attendance" (
"attendance_id" INTEGER,
"employee_id" INTEGER,
"attendance_date" TEXT,
"clock_in_time" TEXT,
"clock_out_time" TEXT,
FOREIGN KEY("employee_id") REFERENCES "employee"("employee_id"),
PRIMARY KEY("attendance_id")
);
DROP TABLE IF EXISTS "department";
CREATE TABLE IF NOT EXISTS "department" (
"department_id" INTEGER,
"department_name" TEXT,
PRIMARY KEY("department_id")
);
DROP TABLE IF EXISTS "employee";
CREATE TABLE IF NOT EXISTS "employee" (
"employee_id" INTEGER,
"first_name" TEXT,
"last_name" TEXT,
"date_of_birth" TEXT,
"gender" TEXT,
"contact_number" TEXT,
"email" TEXT,
"address" TEXT,
PRIMARY KEY("employee_id")
);
DROP TABLE IF EXISTS "employeeleave";
CREATE TABLE IF NOT EXISTS "employeeleave" (
"leave_id" INTEGER,
"employee_id" INTEGER,
"leave_type" TEXT,
"start_date" TEXT,
"end_date" TEXT,
"status" TEXT,
FOREIGN KEY("employee_id") REFERENCES "employee"("employee_id"),
PRIMARY KEY("leave_id")
);
DROP TABLE IF EXISTS "jobposition";
CREATE TABLE IF NOT EXISTS "jobposition" (
"position_id" INTEGER,
"position_name" TEXT,
"base_salary" REAL,
PRIMARY KEY("position_id")
);
DROP TABLE IF EXISTS "payroll";
CREATE TABLE IF NOT EXISTS "payroll" (
"payroll_id" INTEGER,
"employee_id" INTEGER,
"salary_month" TEXT,
"salary_year" INTEGER,
"total_salary" REAL,
"deductions" REAL,
"bonuses" REAL,
"net_salary" REAL,
FOREIGN KEY("employee_id") REFERENCES "employee"("employee_id"),
PRIMARY KEY("payroll_id")
);
DROP TABLE IF EXISTS "earning_type";
CREATE TABLE IF NOT EXISTS "earning_type" (
"earning_type_id" INTEGER,
"type_name" TEXT,
PRIMARY KEY("earning_type_id")
);
DROP TABLE IF EXISTS "earnings";
CREATE TABLE IF NOT EXISTS "earnings" (
"earning_id" INTEGER,
"payroll_id" INTEGER,
"earning_type_id" INTEGER,
"amount" REAL,
FOREIGN KEY("payroll_id") REFERENCES "payroll"("payroll_id"),
FOREIGN KEY("earning_type_id") REFERENCES "earning_type"("earning_type_id"),
PRIMARY KEY("earning_id")
);
DROP TABLE IF EXISTS "deduction_type";
CREATE TABLE IF NOT EXISTS "deduction_type" (
"deduction_type_id" INTEGER,
"type_name" TEXT,
PRIMARY KEY("deduction_type_id")
);
DROP TABLE IF EXISTS "deductions";
CREATE TABLE IF NOT EXISTS "deductions" (
"deduction_id" INTEGER,
"payroll_id" INTEGER,
"deduction_type_id" INTEGER,
"amount" REAL,
PRIMARY KEY("deduction_id"),
FOREIGN KEY("payroll_id") REFERENCES "payroll"("payroll_id"),
FOREIGN KEY("deduction_type_id") REFERENCES "deduction_type"("deduction_type_id")
);
DROP TABLE IF EXISTS "department_jobposition";
CREATE TABLE IF NOT EXISTS "department_jobposition" (
"department_id" INTEGER,
"position_id" INTEGER,
PRIMARY KEY("department_id","position_id"),
FOREIGN KEY("department_id") REFERENCES "department"("department_id") ON DELETE CASCADE,
FOREIGN KEY("position_id") REFERENCES "jobposition"("position_id") ON DELETE CASCADE
);
DROP TABLE IF EXISTS "employee_department";
CREATE TABLE IF NOT EXISTS "employee_department" (
"employee_id" INTEGER,
"department_id" INTEGER,
"start_date" TEXT,
"end_date" TEXT,
PRIMARY KEY("employee_id","department_id"),
FOREIGN KEY("employee_id") REFERENCES "employee"("employee_id") ON DELETE CASCADE,
FOREIGN KEY("department_id") REFERENCES "department"("department_id") ON DELETE CASCADE
);
DROP TABLE IF EXISTS "employee_jobposition";
CREATE TABLE IF NOT EXISTS "employee_jobposition" (
"employee_id" INTEGER,
"position_id" INTEGER,
"start_date" TEXT,
"end_date" TEXT,
PRIMARY KEY("employee_id","position_id"),
FOREIGN KEY("position_id") REFERENCES "jobposition"("position_id") ON DELETE CASCADE,
FOREIGN KEY("employee_id") REFERENCES "employee"("employee_id") ON DELETE CASCADE
);
INSERT INTO "department" VALUES (2,'Apotek');
INSERT INTO "employee" VALUES (1,'yaya','yeye','2021-08-22','Perempuan','0822261672','[email protected]','sungai ayak');
INSERT INTO "employee" VALUES (2,'rara','rere','2023-03-27','Perempuan','0882342','[email protected]','sungai ayak');
INSERT INTO "jobposition" VALUES (1,'dokter',NULL);
INSERT INTO "jobposition" VALUES (2,'bidan',NULL);
INSERT INTO "jobposition" VALUES (3,'apoteker',NULL);
INSERT INTO "earning_type" VALUES (1,'Overtime');
INSERT INTO "earning_type" VALUES (2,'Bonus');
INSERT INTO "deduction_type" VALUES (1,'Tax');
INSERT INTO "deduction_type" VALUES (2,'Insurance');
INSERT INTO "department_jobposition" VALUES (1,1);
INSERT INTO "department_jobposition" VALUES (1,2);
INSERT INTO "department_jobposition" VALUES (2,3);
INSERT INTO "employee_department" VALUES (1,1,'2023-12-01','');
INSERT INTO "employee_department" VALUES (2,2,'2023-12-01','');
INSERT INTO "employee_jobposition" VALUES (1,2,'2023-12-08','');
COMMIT;
$active_group = 'default';
$query_builder = TRUE;
$db['default'] = array(
'dsn' => '',
'hostname' => '',
'username' => '',
'password' => '',
'database' => './application/maria_ems.db',
'dbdriver' => 'sqlite3',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE,
'dbdriver_options' => array(
'sqlite3' => array(
'dbdriver' => 'sqlite3',
'database' => APPPATH . 'maria_ems.db',
'dbprefix' => '',
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE,
'flags' => SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE,
'pragma' => array(
'foreign_keys' => TRUE, // Example pragma setting
// Add other pragma settings as needed
),
),
)
);
public function delete_data_department($id)
{
try {
$this->db->trans_start(); // Start transaction
// Enable foreign keys using PRAGMA
$this->db->query('PRAGMA foreign_keys = ON');
$this->db->where('department_id', $id);
$this->db->delete('department');
if ($this->db->affected_rows() > 0) {
$this->db->trans_commit(); // Commit transaction
echo "Data Berhasil dihapus!";
} else {
$this->db->trans_rollback(); // Rollback transaction
http_response_code(400);
echo json_encode(["error" => "Ada yang tidak beres, hubungi developer anda!"]);
}
} catch (Exception $e) {
// Handle exceptions, if any
$this->db->trans_rollback(); // Rollback transaction
http_response_code(500);
echo json_encode(["error" => $e->getMessage()]);
}
}