Όταν το κάνετε:
DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1
Σφάλματα:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails
(paymesomething.advertisers, CONSTRAINT advertisers_ibfk_1 FOREIGN KEY
(advertiser_id) REFERENCES jobs (advertiser_id))
Εδώ είναι οι πίνακές μου:
CREATE TABLE IF NOT EXISTS `advertisers` (
`advertiser_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`password` char(32) NOT NULL,
`email` varchar(128) NOT NULL,
`address` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`fax` varchar(255) NOT NULL,
`session_token` char(30) NOT NULL,
PRIMARY KEY (`advertiser_id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
INSERT INTO `advertisers` (`advertiser_id`, `name`, `password`, `email`, `address`, `phone`, `fax`, `session_token`) VALUES
(1, 'TEST COMPANY', '', '', '', '', '', '');
CREATE TABLE IF NOT EXISTS `jobs` (
`job_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`advertiser_id` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`shortdesc` varchar(255) NOT NULL,
`longdesc` text NOT NULL,
`address` varchar(255) NOT NULL,
`time_added` int(11) NOT NULL,
`active` tinyint(1) NOT NULL,
`moderated` tinyint(1) NOT NULL,
PRIMARY KEY (`job_id`),
KEY `advertiser_id` (`advertiser_id`,`active`,`moderated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
INSERT INTO `jobs` (`job_id`, `advertiser_id`, `name`, `shortdesc`, `longdesc`, `address`, `active`, `moderated`) VALUES
(1, 1, 'TEST', 'TESTTEST', 'TESTTESTES', '', 0, 0);
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`);
Ως έχει, πρέπει να διαγράψετε τη γραμμή από τον πίνακα διαφημιζόμενων πριν διαγράψετε τη γραμμή στον πίνακα θέσεων εργασίας στην οποία παραπέμπει. Αυτό:
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`)
REFERENCES `jobs` (`advertiser_id`);
...είναι στην πραγματικότητα το αντίθετο από αυτό που θα έπρεπε να είναι. Όπως είναι, σημαίνει ότι θα πρέπει να έχετε μια εγγραφή στον πίνακα jobs πριν από τους διαφημιζόμενους. Έτσι πρέπει να χρησιμοποιήσετε:
ALTER TABLE `jobs`
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`)
REFERENCES `advertisers` (`advertiser_id`);
Μόλις διορθώσετε τη σχέση ξένου κλειδιού, η δήλωση διαγραφής σας θα λειτουργήσει.
Σύμφωνα με τον τρέχοντα (ενδεχομένως εσφαλμένο) σχεδιασμό σας, πρέπει να διαγράψετε τη γραμμή από τον πίνακα διαφημιζόμενων πριν διαγράψετε τη γραμμή στον πίνακα θέσεων εργασίας στην οποία παραπέμπει.
Εναλλακτικά, θα μπορούσατε να ρυθμίσετε το ξένο κλειδί σας έτσι ώστε μια διαγραφή στον πίνακα-γονέα να προκαλεί αυτόματη διαγραφή των γραμμών στους πίνακες-παιδιά. Αυτό ονομάζεται διαδοχική διαγραφή. Μοιάζει κάπως έτσι:
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1`
FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`)
ON DELETE CASCADE;
Τούτου λεχθέντος, όπως έχουν ήδη επισημάνει άλλοι, το ξένο κλειδί σας φαίνεται ότι θα έπρεπε να πηγαίνει αντίστροφα, αφού ο πίνακας διαφημιζόμενων περιέχει πραγματικά το πρωτεύον κλειδί και ο πίνακας θέσεων εργασίας περιέχει το ξένο κλειδί. Εγώ θα το ξαναέγραφα ως εξής:
ALTER TABLE `jobs`
ADD FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`);
Και το cascading delete δεν θα είναι απαραίτητο.
Εάν υπάρχουν περισσότερες από μία εργασίες που έχουν το ίδιο advertiser_id, τότε το ξένο κλειδί σας θα πρέπει να είναι:
ALTER TABLE `jobs`
ADD CONSTRAINT `advertisers_ibfk_1`
FOREIGN KEY (`advertiser_id`)
REFERENCES `advertisers` (`advertiser_id`);
Διαφορετικά (αν συμβαίνει το αντίθετο στην περίπτωσή σας), αν θέλετε οι γραμμές στο advertiser να διαγράφονται αυτόματα αν διαγραφεί η γραμμή στο job προσθέστε την επιλογή 'ON DELETE CASCADE' στο τέλος του ξένου κλειδιού σας:
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1`
FOREIGN KEY (`advertiser_id`)
REFERENCES `jobs` (`advertiser_id`)
ON DELETE CASCADE;
Ελέγξτε το Περιορισμοί ξένου κλειδιού