Jinsi ya Kurekebisha MySQL Error #1451 Cannot Delete or Update a Parent Row

Unapofanya kazi na database ya MySQL yenye tables zinazohusiana, unaweza kukutana na kosa hili:

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

Kosa hili hutokea unapojaribu kufuta record kwenye table kuu (parent table) lakini bado kuna table nyingine inayotegemea record hiyo kupitia foreign key.

Kwa mfano unaweza kuona kosa kama hili:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails
(`database`.`teacher_subject_assignments`,
CONSTRAINT `teacher_subject_assignments_ibfk_1`
FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`))

Hii ina maana kwamba:

Unajaribu kufuta user kwenye table users

Lakini table teacher_subject_assignments bado inamtumia user huyo

MySQL inalinda database yako ili data isipotee au kuvurugika.

Suluhisho la Kwanza: Futa Data Zinazomtegemea User

Kabla ya kufuta user, lazima kwanza ufute records zinazomtegemea kwenye table nyingine.

Hatua ya 1

Futa assignments zinazotumia user huyo:

DELETE FROM teacher_subject_assignments
WHERE teacher_id = 5;
Hatua ya 2

Baada ya hapo futa user:

DELETE FROM users
WHERE id = 5;
Suluhisho la Pili (Bora Zaidi): Tumia ON DELETE CASCADE

Njia ya kitaalamu zaidi ni kutumia ON DELETE CASCADE ili MySQL ifute records zinazohusiana automatically.

Hatua ya 1: Ondoa foreign key ya zamani
ALTER TABLE teacher_subject_assignments
DROP FOREIGN KEY teacher_subject_assignments_ibfk_1;
Hatua ya 2: Tengeneza foreign key mpya
ALTER TABLE teacher_subject_assignments
ADD CONSTRAINT teacher_subject_assignments_ibfk_1
FOREIGN KEY (teacher_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Baada ya hapo ukifanya:

DELETE FROM users WHERE id = 5;

MySQL itafuta pia records zote zinazomtegemea user huyo kwenye table nyingine.

Jinsi ya Kuona Tables Zote Zinazotegemea Users

Kama database yako ina tables nyingi, unaweza kuangalia tables zote zinazotegemea users kwa kutumia query hii:

SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'users';

Hii itakuonyesha tables zote zinazotumia users.id kama foreign key.

Ushauri kwa Developers

Developers wengi hutumia:

ON DELETE CASCADE

Kwa sababu:

inaepusha database errors

inaweka database safi

inaokoa muda wa kufuta records moja moja

inarahisisha usimamizi wa database

Lakini hakikisha unaelewa mahusiano ya tables kabla ya kutumia cascade delete.

Hitimisho

Kosa la MySQL #1451 Cannot delete or update a parent row hutokea kwa sababu record unayotaka kufuta bado inatumika kwenye table nyingine.

Unaweza kulitatua kwa:

Kufuta records zinazotegemea data hiyo kwanza

Kubadilisha reference kwenda kwa user mwingine

Kutumia ON DELETE CASCADE ili MySQL ifute records zinazohusiana automatically

Kuelewa foreign keys ni muhimu sana kwa kujenga database salama na yenye ufanisi.

Jifunze Zaidi

Kwa mafunzo zaidi kuhusu:

PHP programming

MySQL database

Web development

Mfumo wa shule na biashara

Tembelea:

👉 https://faulink.com

Faulink inatoa mafunzo na mifumo ya kusaidia developers na wafanyabiashara kujenga mifumo bora ya kidigitali.