Jinsi ya Kufuta (Delete/Drop) Tables Zinazogoma Kufutwa Katika MySQL – Sababu, Suluhisho na Mifano Halisi

Moja ya matatizo yanayowakabili watengenezaji wa PHP na MySQL ni pale wanapotaka kufuta table lakini MySQL inakataa.

Mfano:

DROP TABLE students;

kisha unapata error:

#1451 - Cannot delete or update a parent row:
a foreign key constraint fails

au:

#1051 Unknown table

au:

Table is locked.

Tatizo hili linawachanganya sana wanafunzi wa programming na hata baadhi ya developers.

Katika makala hii tutajifunza:

Kwa nini table linagoma kufutwa.
Jinsi ya kugundua tatizo.
Suluhisho la kila aina ya error.
Mifano halisi ya miradi ya shule na biashara.
Sababu ya Kwanza: Foreign Key Constraint

Mfano:

Una table mbili:

students
marks

Table la marks linategemea students.

Mfano:

CREATE TABLE students(
id INT PRIMARY KEY,
name VARCHAR(100)
);

na:

CREATE TABLE marks(
id INT PRIMARY KEY,
student_id INT,
FOREIGN KEY(student_id)
REFERENCES students(id)
);

Ukijaribu:

DROP TABLE students;

utapata:

Cannot delete or update a parent row.
Suluhisho la Kwanza

Futa table linalotegemea kwanza.

DROP TABLE marks;
DROP TABLE students;
Suluhisho la Pili

Zima Foreign Key Checks.

SET FOREIGN_KEY_CHECKS=0;

Kisha:

DROP TABLE students;

Mwisho:

SET FOREIGN_KEY_CHECKS=1;

Hii ni moja ya code zinazotumika sana na professional developers.

Mfano Halisi wa Mfumo wa Shule

Tables:

students
subjects
marks
payments
classes

marks inategemea students.

payments inategemea students.

Ukifuta:

DROP TABLE students;

itakataa.

Suluhisho:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE marks;
DROP TABLE payments;
DROP TABLE students;

SET FOREIGN_KEY_CHECKS=1;
Sababu ya Pili: Table Locked

Mfano:

User mmoja anaingiza marks.

User mwingine anataka kufuta table.

MySQL inaweza kusema:

Table is locked.
Suluhisho

Angalia processes:

SHOW PROCESSLIST;

Kisha:

KILL process_id;

Mfano:

KILL 45;

Baada ya hapo:

DROP TABLE students;
Sababu ya Tatu: Unknown Table

Mfano:

DROP TABLE student;

lakini table ni:

students

Utapata:

Unknown table
Suluhisho

Angalia tables:

SHOW TABLES;

Kisha:

DROP TABLE students;
Sababu ya Nne: Database Haijachaguliwa

Error:

No database selected
Suluhisho
USE school_system;

kisha:

DROP TABLE students;
Sababu ya Tano: Huna Ruhusa

Error:

DROP command denied.
Suluhisho

Msimamizi wa server atoe ruhusa:

GRANT ALL PRIVILEGES
ON school_system.*
TO 'user'@'localhost';

kisha:

FLUSH PRIVILEGES;
Kufuta Tables Nyingi Zinazogoma

Mfano:

students
marks
subjects
payments
teachers

Code:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS
marks,
payments,
subjects,
teachers,
students;

SET FOREIGN_KEY_CHECKS=1;

Hii ni code inayotumiwa sana.

Kufuta Database Yote
DROP DATABASE school_system;

Lakini kama kuna foreign keys:

SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE school_system;
SET FOREIGN_KEY_CHECKS=1;
Kufuta Table Kupitia phpMyAdmin
Chagua database.
Chagua table.
Bonyeza Drop.

Ikiwa linagoma:

Fungua SQL tab.
Andika:
SET FOREIGN_KEY_CHECKS=0;
Kisha:
DROP TABLE students;
Mfano Halisi wa Mfumo wa Uhasibu

Tables:

uhasibu_students
uhasibu_contributions
uhasibu_classes
contribution_types

Tatizo:

Cannot delete or update parent row.

Suluhisho:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE uhasibu_contributions;
DROP TABLE uhasibu_students;

SET FOREIGN_KEY_CHECKS=1;
Jinsi ya Kuona Foreign Keys
SHOW CREATE TABLE marks;

au:

SHOW CREATE TABLE uhasibu_contributions;

Hii itaonyesha foreign key zote.

Jinsi ya Kufuta Foreign Key Peke Yake
ALTER TABLE marks
DROP FOREIGN KEY marks_ibfk_1;

Baada ya hapo:

DROP TABLE students;
Kufuta Tables Zote Ndani ya Database
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS
students,
teachers,
subjects,
marks,
payments,
classes,
users,
blogs,
comments;

SET FOREIGN_KEY_CHECKS=1;
Vidokezo Muhimu
Fanya Backup
Export → Quick → Go
Tumia IF EXISTS
DROP TABLE IF EXISTS students;
Angalia Foreign Keys
SHOW CREATE TABLE table_name;
Zima Foreign Key Checks Kwa Tahadhari
SET FOREIGN_KEY_CHECKS=0;
Hitimisho

Sababu kubwa zinazofanya table zigome kufutwa ni:

Foreign Key Constraints.
Table Locked.
Unknown Table.
No Database Selected.
Huna Ruhusa.

Code muhimu zaidi za kutatua tatizo hili ni:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS table_name;
SET FOREIGN_KEY_CHECKS=1;

na:

SHOW PROCESSLIST;
KILL process_id;
SHOW CREATE TABLE table_name;

Kwa kujifunza commands hizi utaweza kusimamia databases kubwa za shule, biashara, uhasibu, hospitali, websites, na mifumo mingine ya kisasa bila matatizo ya tables zinazokataa kufutwa.

Tembelea https://faulink.com kwa mafunzo zaidi ya PHP, MySQL, Websites, na Software Development Tanzania.