Jinsi ya Kufuta Tables Zinazogoma Kufutwa Katika MySQL – Suluhisho Kamili Kwa Waanzilishi na Wataalamu

Katika maendeleo ya mifumo ya kompyuta kwa kutumia PHP na MySQL, kuna wakati developer anahitaji kufuta table moja au tables nyingi ndani ya database. Hata hivyo, mara nyingi unapoandika:

DROP TABLE students;

MySQL inaweza kurudisha ujumbe wa makosa kama:

Cannot delete or update a parent row

au:

Table is locked

au:

DROP command denied

au:

Unknown table

Matatizo haya huwakuta wanafunzi wa programming, web developers, na hata wataalamu wenye uzoefu mkubwa.

Katika makala hii tutajifunza:

Sababu zote zinazofanya table ligome kufutwa.
Njia za kutambua tatizo.
Suluhisho la kila tatizo.
Mifano halisi ya mifumo ya shule, biashara, hospitali na accounting.
Code za SQL zinazofanya kazi.
Kuelewa DROP TABLE

Command ya:

DROP TABLE students;

hufanya mambo matatu:

Hufuta data zote.
Hufuta columns zote.
Hufuta table lote kabisa.

Baada ya kutumia DROP TABLE huwezi kurejesha taarifa zako isipokuwa kama ulifanya backup.

Sababu ya Kwanza: Foreign Key Constraints

Hili ndilo tatizo linalotokea mara nyingi zaidi.

Fikiria una tables hizi:

students
id name
1 John
2 Peter
marks
id student_id score
1 1 90
2 2 85

Table la marks linategemea table la students.

Kutengeneza Tables
CREATE TABLE students(
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE marks(
id INT PRIMARY KEY,
student_id INT,
score INT,
FOREIGN KEY(student_id)
REFERENCES students(id)
);

Ukijaribu:

DROP TABLE students;

utapata:

#1451 Cannot delete or update a parent row
Kwa Nini Hii Inatokea?

MySQL inalinda data zako.

Kama student anafutwa wakati marks zake bado zipo, database inaweza kuharibika.

Ndiyo maana MySQL hukataa kufuta table la parent.

Suluhisho la Kwanza

Futa child table kwanza.

DROP TABLE marks;
DROP TABLE students;
Suluhisho la Pili

Zima Foreign Key Checks.

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE students;

SET FOREIGN_KEY_CHECKS=1;
Suluhisho la Tatu

Futa tables zote kwa wakati mmoja.

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE marks, students;

SET FOREIGN_KEY_CHECKS=1;
Mfano Halisi wa Mfumo wa Shule

Database:

students
subjects
marks
payments
attendance
classes

Mahusiano:

marks → students
payments → students
attendance → students

Ukijaribu:

DROP TABLE students;

itashindikana.

Suluhisho:

SET FOREIGN_KEY_CHECKS=0;

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

SET FOREIGN_KEY_CHECKS=1;
Sababu ya Pili: Table is Locked

Mara nyingi hutokea katika mifumo yenye watumiaji wengi.

Mfano:

Mwalimu anaingiza marks.
Admin anataka kufuta table.

MySQL inaweza kusema:

Table is locked.
Jinsi ya Kugundua Tatizo
SHOW PROCESSLIST;

Mfano:

Id User Command
52 root Query
60 teacher Sleep
Kufunga Process
KILL 60;

Baada ya hapo:

DROP TABLE students;

itafanya kazi.

Sababu ya Tatu: Unknown Table

Mfano:

Table ni:

students

lakini umeandika:

DROP TABLE student;

Utapata:

Unknown table 'student'
Suluhisho
SHOW TABLES;

Matokeo:

students
teachers
subjects

Kisha:

DROP TABLE students;
Sababu ya Nne: No Database Selected

Error:

No database selected.
Suluhisho
USE school_system;

kisha:

DROP TABLE students;
Sababu ya Tano: Huna Ruhusa

Error:

DROP command denied.

Hutokea sana kwenye shared hosting.

Suluhisho

Admin wa server atoe ruhusa:

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

Kisha:

FLUSH PRIVILEGES;
Sababu ya Sita: Table Inatumika na Transaction

Mfano:

START TRANSACTION;

kisha:

UPDATE students
SET name='John'
WHERE id=1;

Wakati huo user mwingine anataka kufuta table.

MySQL inaweza kukataa.

Suluhisho
COMMIT;

au:

ROLLBACK;
Sababu ya Saba: Corrupted Table

Wakati mwingine table linaweza kuharibika.

Error:

Table is marked as crashed.
Suluhisho
REPAIR TABLE students;

Baada ya hapo:

DROP TABLE students;
Kufuta Foreign Key Pekee

Kwanza angalia:

SHOW CREATE TABLE marks;

Matokeo:

CONSTRAINT marks_ibfk_1
FOREIGN KEY(student_id)
REFERENCES students(id)

Futa constraint:

ALTER TABLE marks
DROP FOREIGN KEY marks_ibfk_1;

Kisha:

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

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

SET FOREIGN_KEY_CHECKS=1;
Mifano Halisi ya Mfumo wa Accounting

Tables:

uhasibu_students
uhasibu_classes
uhasibu_contributions
contribution_types
payments

Mahusiano:

uhasibu_contributions → uhasibu_students
payments → uhasibu_students

Code:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE
payments,
uhasibu_contributions,
uhasibu_students,
uhasibu_classes,
contribution_types;

SET FOREIGN_KEY_CHECKS=1;
Mifano Halisi ya Mfumo wa Hospitali

Tables:

patients
doctors
appointments
payments
prescriptions

Mahusiano:

appointments → patients
payments → patients
prescriptions → patients

Suluhisho:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE
appointments,
payments,
prescriptions,
patients,
doctors;

SET FOREIGN_KEY_CHECKS=1;
Mifano Halisi ya Mfumo wa E-commerce

Tables:

customers
orders
order_items
payments
products

Suluhisho:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE
order_items,
orders,
payments,
customers,
products;

SET FOREIGN_KEY_CHECKS=1;
Kutumia phpMyAdmin

Hatua:

Fungua phpMyAdmin.
Chagua database.
Chagua SQL tab.
Andika:
SET FOREIGN_KEY_CHECKS=0;
Kisha:
DROP TABLE students;
Mwisho:
SET FOREIGN_KEY_CHECKS=1;
Jinsi ya Kufanya Backup Kabla ya Kufuta
Chagua database.
Bonyeza Export.
Chagua Quick.
Bonyeza Go.

Faili la SQL litapakuliwa.

Ukikosea unaweza kurejesha data.

Makosa Makubwa ya Kuepuka
1. Kufuta bila Backup.
2. Kuzima FOREIGN_KEY_CHECKS bila kujua unachofanya.
3. Kufuta production database.
4. Kutumia DROP badala ya DELETE.
5. Kutofanya majaribio kwenye localhost kwanza.
Code Muhimu Zaidi Kwa Developers
SHOW TABLES;
SHOW PROCESSLIST;
SHOW CREATE TABLE students;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS students;
REPAIR TABLE students;
KILL process_id;
ALTER TABLE marks
DROP FOREIGN KEY marks_ibfk_1;
Maswali Yanayoulizwa Mara Kwa Mara (FAQ)
Je, DROP TABLE hufuta data zote?

Ndiyo.

Je, naweza kurejesha table baada ya DROP?

Ndiyo, kama una backup.

Je, FOREIGN_KEY_CHECKS=0 ni salama?

Ndiyo, lakini tumia kwa tahadhari.

Je, naweza kufuta tables zote kwa command moja?

Ndiyo.

DROP TABLE table1, table2, table3;
Hitimisho

Zaidi ya asilimia kubwa ya matatizo ya tables zinazogoma kufutwa katika MySQL husababishwa na:

Foreign Key Constraints.
Locked Tables.
Unknown Table.
No Database Selected.
Permission Errors.
Corrupted Tables.
Transactions ambazo hazijafungwa.

Code inayotumika sana na wataalamu ni:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS
table1,
table2,
table3;

SET FOREIGN_KEY_CHECKS=1;

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

Mwandishi: Faulink Team
Website: https://faulink.com
Mafunzo: PHP, MySQL, Websites, School Systems, Accounting Systems, na Software Development Tanzania.