basics mysql foreign key constraint with examples
Αυτό το σεμινάριο εξηγεί τα βασικά του MySQL FOREIGN KEY Constraint, όπως τη σύνταξή του, τον τρόπο προσθήκης, δήλωσης, απόθεσης και αλλαγής του με παραδείγματα:
Με πολύ απλούς όρους, το FOREIGN KEY χρησιμοποιείται για τη σύνδεση δύο ή περισσότερων πινάκων στη MySQL.
Οι πίνακες MySQL πρέπει να συνδεθούν για να υποβάλουν ερωτήματα και να ενημερώσουν διάφορους τύπους δεδομένων σε διαφορετικά χρονικά σημεία. Ως εκ τούτου, είναι επιτακτική ανάγκη να υπάρχει ένα σημείο σύνδεσης μεταξύ 2 πινάκων.
Σε αυτό το σεμινάριο, θα συζητήσουμε διαφορετικές χρήσεις ξένων κλειδιών και πώς μπορεί να δηλωθεί και να αλλάξει, και ποιοι περιορισμοί έχει στη συνολική δομή του πίνακα.
Τι θα μάθετε:
ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ MySQL
Σύνταξη:
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option} Πάνω είναι η σύνταξη που χρησιμοποιείται κατά τον καθορισμό ΞΕΝΟ ΚΛΕΙΔΙ σε έναν πίνακα κατά τη δημιουργία πίνακα ή με Δήλωση ALTER TABLE.
Ας κατανοήσουμε τα διάφορα στοιχεία της σύνταξης:
- όνομα Αυτό είναι το συμβολικό όνομα που θέλουμε να ορίσουμε για τον καθορισμό του περιορισμού FK. Εάν παραλειφθεί, τότε ο κινητήρας MySQL εκχωρεί αυτόματα ένα όνομα στον περιορισμό FK.
- αναφοράColumnName: Αυτή είναι η στήλη που θα αναφέρεται στις τιμές σε έναν άλλο πίνακα, όπως καθορίζεται από τη στήλη στον αναφερόμενο πίνακα.
- Αναφερόμενος πίνακας / γονικός πίνακας: Αυτό αναφέρεται στο όνομα του πίνακα από τον οποίο θα αναφέρονται οι τιμές.
- Αναφερόμενη στήλη: Το όνομα της στήλης στον αναφερόμενο πίνακα.
- Επιλογή αναφοράς: Αυτές είναι οι ενέργειες που εμφανίζονται στην εικόνα όταν πραγματοποιείται μια ενέργεια ενημέρωσης ή διαγραφής στον πίνακα που κρατά τον περιορισμό ξένου κλειδιού. Τόσο η ΕΝΗΜΕΡΩΣΗ όσο και η ΔΙΑΓΡΑΦΗ μπορούν να έχουν τις ίδιες ή διαφορετικές επιλογές αναφοράς.
Θα μάθαμε για διάφορες ενέργειες ακεραιότητας αναφοράς σε αυτό το σεμινάριο.
Ας δούμε ένα παράδειγμα αναφοράς ΞΕΝΟ ΚΛΕΙΔΙ χρησιμοποιώντας το παράδειγμα Υπάλληλος / Τμήμα. Θα δημιουργήσουμε έναν πίνακα Τμήμα με στήλες - departmentId (int & PRIMARY KEY) και departmentName (varchar).
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));Δημιουργήστε έναν πίνακα Υπάλληλος με στήλες όπως παρακάτω:
| Στήλη | Τύπος |
|---|---|
| ταυτότητα | INT (Κύριο κλειδί) |
| όνομα | ΒΑΡΚΑΡ |
| dept_id | Αναφορά INT (Ξένο Κλειδί) από τον πίνακα τμημάτων |
| διεύθυνση | ΒΑΡΚΑΡ |
| ηλικία | ΙΝΤ |
| dob | ΗΜΕΡΟΜΗΝΙΑ |
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE); Όπως μπορείτε να δείτε, στον παραπάνω πίνακα υπαλλήλων, έχουμε δηλώσει τη στήλη deptId του τύπου Int και ορίσαμε ΞΕΝΟ ΚΛΕΙΔΙ από τον πίνακα του τμήματος στη στήλη departmentId.
Αυτό σημαίνει ουσιαστικά ότι η στήλη deptId στον πίνακα Υπάλληλος μπορεί να περιέχει μόνο τιμές που βρίσκονται στον πίνακα Τμήμα.
Ας προσπαθήσουμε να εισαγάγουμε δεδομένα σε αυτούς τους πίνακες και να δούμε πώς λειτουργεί το ΕΞΩΤΕΡΙΚΟ ΠΛΗΡΟΦΟΡΙΕΣ.
- Δημιουργήστε πρώτα μια εγγραφή στον πίνακα Τμήματος και προσθέστε μια εγγραφή στον πίνακα Υπάλληλος που αναφέρεται στο αναγνωριστικό της εγγραφής που προστέθηκε στον πίνακα Τμήματος.
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1); Θα δείτε ότι και οι δύο δηλώσεις θα εκτελεστούν χωρίς σφάλματα.
- Τώρα αναφέρετε μια τιμή για το departmentId που δεν υπάρχει.
Για παράδειγμα, στην παρακάτω δήλωση ερωτήματος, δημιουργούμε έναν υπάλληλο με ένα μη υπάρχον τμήμαId -10
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);- Σε αυτό το σενάριο, θα λάβουμε ένα σφάλμα όπως παρακάτω:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)Έτσι, σε γενικές γραμμές, όταν ορίζονται οι παραπομπές ΞΕΝΟΥ ΚΛΕΙΔΙΟΥ, είναι σημαντικό να διασφαλιστεί ότι ο πίνακας που αναφέρεται πρέπει να έχει δεδομένα πριν από την αναφορά.
Ενδεικτικές ενέργειες ακεραιότητας
Ας προσπαθήσουμε πρώτα να καταλάβουμε τι ακριβώς είναι η ακεραιότητα αναφοράς.
Η Ακεραιότητα Αναφοράς βοηθά στη διατήρηση δεδομένων σε καθαρή και συνεπή κατάσταση όπου υπάρχουν πίνακες που σχετίζονται μεταξύ τους με μια σχέση ΞΕΝΟΥ ΚΛΕΙΔΙΟΥ.
Με απλά λόγια, η Ακεραιότητα Αναφοράς αναφέρεται στη Δράση που περιμένουμε από τη μηχανή βάσης δεδομένων, όταν συμβαίνει ΕΝΗΜΕΡΩΣΗ ή ΔΙΑΓΡΑΦΗ στον πίνακα αναφοράς που περιέχει το ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ.
Για παράδειγμα, στο παράδειγμα υπαλλήλου / τμήματος, ας υποθέσουμε ότι αλλάζουμε το αναγνωριστικό τμήματος για μια συγκεκριμένη σειρά στο DB. Τότε θα επηρεαστούν όλες οι σειρές αναφοράς στον πίνακα υπαλλήλων. Μπορούμε να ορίσουμε διαφορετικούς τύπους σεναρίων ακεραιότητας αναφοράς που θα μπορούσαν να εφαρμοστούν σε τέτοιες περιπτώσεις.
τα πιο δημοφιλή λειτουργικά συστήματα για προσωπικούς υπολογιστές
Σημείωση: Η Ακεραιότητα Αναφοράς ορίζεται κατά τη ρύθμιση / δήλωση ΞΕΝΟΥ ΚΛΕΙΔΙΟΥ ως μέρος των εντολών / ενοτήτων ON DELETE και ON UPDATE.
Ανατρέξτε σε ένα δείγμα ερωτήματος εδώ (για παράδειγμα το Υπάλληλος / Τμήμα):
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100)); CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE {ref-integrity-action} ON DELETE {ref integrity action}); Εισαγάγετε ορισμένα δεδομένα σε αυτούς τους πίνακες όπως παρακάτω:
INSERT INTO department VALUES (1, 'ENGINEERING'), (2,'ACCOUNTING'), (3, 'MARKETING'), (4, 'HR'), (5, 'LEGAL'); INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32,'1988-02-12',1), (2, 'RYAN HILMAN', 'SEATTLE',43, '1977-03-15',1), (3, 'KAVITA SINGH', 'DELHI', 42, '1978-02-18',4), (4, 'DAVID BECKHAM', 'LONDON', 40, '1980-07-13',3), (5, 'PRITI KUMARI', 'DELHI', 35, '1985-12-11',2), (6, 'FRANK BALDING', 'NEW YORK', 35, '1985-08-25',5) Υπάρχουν 4 ενέργειες αναφοράς που υποστηρίζονται από τη MySQL. Ας προσπαθήσουμε να κατανοήσουμε κάθε ένα από αυτά.
# 1) CASCADE
Αυτή είναι μια από τις πιο συχνά χρησιμοποιούμενες Ενέργειες Αναφοράς Ακεραιότητας. Η ρύθμιση DELETE και UPDATE σε CASCADE θα εφαρμόσει τις αλλαγές που έγιναν στον πίνακα αναφοράς στον πίνακα αναφοράς, δηλαδή στο παράδειγμα Υπάλληλος / Τμήμα. Ας υποθέσουμε ότι κάποιος διαγράφει μια σειρά στον πίνακα Τμήματος που πρέπει να λέει department_name = ΛΟΓΙΣΤΙΚΟ, και τότε θα διαγραφούν επίσης όλες οι σειρές στον πίνακα υπαλλήλων με τμήμα_id όπως αυτός του πίνακα Λογιστικής.
Ας το καταλάβουμε με ένα παράδειγμα:
SELECT * FROM employee;| ταυτότητα | όνομα | διεύθυνση | ηλικία | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | ΜΟΥΜΠΑΙ | 32 | 1988-02-12 | 1 |
| δύο | ΡΥΑΝ ΧΙΛΜΑΝ | ΚΑΘΙΣΜΑ | 43 | 1977-03-15 | 1 |
| 3 | KAVITA SINGH | ΔΕΛΧΙ | 42 | 1978-02-18 | 4 |
| 4 | ΝΤΕΪΒΙΝΤ ΜΠΕΚΑΜ | ΛΟΝΔΙΝΟ | 40 | 1980-07-13 | 3 |
| 5 | ΠΡΙΤΗ ΚΟΥΜΑΡΙ | ΔΕΛΧΙ | 35 | 1985-12-11 | δύο |
| 6 | ΦΡΑΝΚ ΜΠΑΛΙΔΑ | ΝΕΑ ΥΟΡΚΗ | 35 | 1985-08-25 | 5 |
Διαγραφή εγγραφής από τον πίνακα τμήματος όπου departmentName = «ΛΟΓΙΣΤΙΚΟΣ»
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';Τώρα, δεδομένου ότι είναι μια ενέργεια αναφοράς CASCADE, θα περιμέναμε να διαγραφούν επίσης όλες οι σειρές που έχουν τμήμαID = 2 (που είναι για το τμήμα 'ΛΟΓΙΣΤΙΚΗ'). Ας κάνουμε ξανά ένα ερώτημα SELECT στον πίνακα υπαλλήλων.
SELECT * FROM employee;| ταυτότητα | όνομα | διεύθυνση | ηλικία | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | ΜΟΥΜΠΑΙ | 32 | 1988-02-12 | 1 |
| δύο | ΡΥΑΝ ΧΙΛΜΑΝ | ΚΑΘΙΣΜΑ | 43 | 1977-03-15 | 1 |
| 3 | KAVITA SINGH | ΔΕΛΧΙ | 42 | 1978-02-18 | 4 |
| 4 | ΝΤΕΪΒΙΝΤ ΜΠΕΚΑΜ | ΛΟΝΔΙΝΟ | 40 | 1980-07-13 | 3 |
| 6 | ΦΡΑΝΚ ΜΠΑΛΙΔΑ | ΝΕΑ ΥΟΡΚΗ | 35 | 1985-08-25 | 5 |
Όπως μπορείτε να δείτε παραπάνω, λόγω της ακεραιότητας αναφοράς του CASCADE, οι σειρές στον πίνακα Υπάλληλοι που αναφέρονται στη διαγραμμένη στήλη ως ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ θα διαγράψουν αυτές τις σειρές.
# 2) ΠΕΡΙΟΡΙΣΜΟΣ / ΟΧΙ ΔΡΑΣΗ
Η λειτουργία ΠΕΡΙΟΡΙΣΜΟΣ ή ΟΧΙ ΕΝΕΡΓΕΙΑ δεν επιτρέπει καμία λειτουργία ΕΝΗΜΕΡΩΣΗΣ ή ΔΙΑΓΡΑΦΗΣ στον πίνακα με στήλες που αναφέρονται ως ΞΕΝΟ ΚΛΕΙΔΙ σε κάποιο πίνακα.
Η λειτουργία NO ACTION μπορεί να εφαρμοστεί απλώς παραλείποντας τους όρους ON UPDATE και ON DELETE από τη δήλωση πίνακα.
Ας δοκιμάσουμε το ίδιο παράδειγμα και σε αυτήν την περίπτωση απλώς παραλείψτε την ενέργεια ΕΝ ΕΝΗΜΕΡΩΣΗ και ΕΝΕΡΓΟΠΟΙΗΣΗ Ακεραιότητας αναφοράς.
Τώρα όταν προσπαθούμε να διαγράψουμε οποιαδήποτε καταχώριση στον πίνακα αναφοράς, θα λάβουμε ένα σφάλμα καθώς έχουμε ορίσει την ενέργεια αναφοράς σε ΠΕΡΙΟΡΙΣΜΟΣ
DELETE FROM department WHERE departmentName='ACCOUNTING';Θα δείτε ένα σφάλμα όπως παρακάτω εάν προσπαθήσετε να εκτελέσετε την παραπάνω εντολή DELETE.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`))# 3) ΡΥΘΜΙΣΗ ΜΗΝ
Με το SET NULL, οποιαδήποτε ΕΝΗΜΕΡΩΣΗ ή ΔΙΑΓΡΑΦΗ στον πίνακα αναφοράς θα προκαλούσε την ενημέρωση μιας τιμής NULL έναντι της τιμής στήλης που έχει επισημανθεί ως ΞΕΝΟ ΚΛΕΙΔΙ στον πίνακα αναφοράς.
Με αυτήν την ενέργεια ακεραιότητας αναφοράς, ο ορισμός του πίνακα υπαλλήλων θα γίνει όπως παρακάτω:
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON DELETE SET NULL); Διαγράψτε μια σειρά στον πίνακα αναφοράς όπως φαίνεται παρακάτω:
DELETE FROM department WHERE departmentName='ACCOUNTING';Τώρα, σε αυτήν την περίπτωση, η τιμή αναφοράς στον πίνακα υπαλλήλων θα οριστεί σε NULL. Κάντε ένα ερώτημα SELECT στον πίνακα Υπάλληλος για να δείτε τα αποτελέσματα.
SELECT * FROM employee;| ταυτότητα | όνομα | διεύθυνση | ηλικία | dob | deptId |
|---|---|---|---|---|---|
| 1 | AMIT KUMAR | ΜΟΥΜΠΑΙ | 32 | 1988-02-12 | 1 |
| δύο | ΡΥΑΝ ΧΙΛΜΑΝ | ΚΑΘΙΣΜΑ | 43 | 1977-03-15 | 1 |
| 3 | KAVITA SINGH | ΔΕΛΧΙ | 42 | 1978-02-18 | 4 |
| 4 | ΝΤΕΪΒΙΝΤ ΜΠΕΚΑΜ | ΛΟΝΔΙΝΟ | 40 | 1980-07-13 | 3 |
| 5 | ΠΡΙΤΗ ΚΟΥΜΑΡΙ | ΔΕΛΧΙ | 35 | 1985-12-11 | ΜΗΔΕΝΙΚΟ |
| 6 | ΦΡΑΝΚ ΜΠΑΛΙΔΑ | ΝΕΑ ΥΟΡΚΗ | 35 | 1985-08-25 | 5 |
# 4) ΡΥΘΜΙΣΤΕ ΤΟ DEFAULT
Η λειτουργία SET DEFAULT όταν καθορίζεται θα έχει ως αποτέλεσμα την αντικατάσταση της προεπιλεγμένης τιμής για τη στήλη (όπως καθορίζεται κατά τη δήλωση στήλης), σε περίπτωση οποιασδήποτε ΔΙΑΓΡΑΦΗΣ στον πίνακα που αναφέρεται.
Σημείωση - Σύμφωνα με Τεκμηρίωση MySQL , η επιλογή SET DEFAULT υποστηρίζεται από το MySQL Parser αλλά όχι από κινητήρες DB όπως το InnoDB. Αυτό μπορεί να υποστηριχθεί στο μέλλον.
Ωστόσο, για να υποστηρίξετε μια τέτοια συμπεριφορά, μπορείτε να χρησιμοποιήσετε το SET NULL και να ορίσετε μια σκανδάλη στον πίνακα που θα μπορούσε να ορίσει μια προεπιλεγμένη τιμή.
Προσθήκη περιορισμού κλειδιού ΕΞΩΤΕΡΙΚΟΥ χρησιμοποιώντας τη δήλωση ALTER TABLE
Πολλές φορές ενδέχεται να θέλουμε να προσθέσουμε έναν περιορισμό ΞΕΝΟ ΚΛΕΙΔΙ σε έναν υπάρχοντα πίνακα που δεν τον έχει.
Ας υποθέσουμε ότι στο παράδειγμα «Υπάλληλος και Τμήμα», δημιουργήσαμε έναν πίνακα υπαλλήλων χωρίς κανένα περιορισμό ΞΕΝΟΥ ΚΛΕΙΔΙΟΥ και αργότερα θέλουμε να εισαγάγουμε τον περιορισμό. Αυτό μπορεί να επιτευχθεί χρησιμοποιώντας την εντολή ALTER TABLE.
Ας προσπαθήσουμε να το καταλάβουμε με ένα παράδειγμα.
Ας υποθέσουμε ότι έχουμε έναν πίνακα υπαλλήλων με τον παρακάτω ορισμό για την εντολή CREATE.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);Εδώ, έχουμε ένα deptId στήλης, αλλά δεν υπάρχει περιορισμός ΚΛΕΙΔΙ ΞΕΝΟΥ. Σε αυτήν την περίπτωση, ακόμη και χωρίς να έχουμε πίνακα Τμημάτων, μπορούμε να καθορίσουμε οποιεσδήποτε τιμές ενώ εισάγουμε εγγραφές.
Τώρα, αργότερα ας υποθέσουμε ότι έχουμε έναν ξεχωριστό πίνακα Τμήματος και θέλουμε να συνδέσουμε το τμήμαΕδώ υπάρχει ως ΞΕΝΟ ΚΛΕΙΔΙ στον πίνακα Υπαλλήλων.
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Τι γίνεται αν αυτός ο πίνακας έχει υπάρχοντα δεδομένα; Μπορούμε να αλλάξουμε πίνακα και να προσθέσουμε περιορισμό ΞΕΝΟΥ ΚΛΕΙΔΙΟΥ;
Η απάντηση είναι ναι - μπορούμε με την προϋπόθεση ότι οι υπάρχουσες τιμές στη στήλη που πρόκειται να αναφερθούν από έναν άλλο πίνακα πρέπει να έχουν εκείνες τις τιμές που υπάρχουν στον ίδιο τον γονικό πίνακα.
Δημιουργήστε έναν πίνακα υπαλλήλων χωρίς περιορισμό ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ, προσθέστε ορισμένα δεδομένα και δοκιμάστε να προσθέσετε έναν περιορισμό ΕΞΩΤΕΡΙΚΟΥ ΚΛΕΙΔΙΟΥ χρησιμοποιώντας την εντολή ALTER.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT); INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',1); INSERT INTO EMPLOYEE VALUES (2, 'ANITA SHERWIN', 'COLUMBIA', 32, '1988-02-12',10); Δημιουργήστε έναν πίνακα τμημάτων και προσθέστε το ΞΕΝΟ ΚΛΕΙΔΙ στο πεδίο «deptId» στον πίνακα υπαλλήλων όπως φαίνεται παρακάτω:
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));Σε αυτό το σημείο, αν προσπαθήσουμε να προσθέσουμε τον περιορισμό ΞΕΝΟ ΚΛΕΙΔΙ,
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; Στη συνέχεια, θα λάβουμε ένα σφάλμα, καθώς ο πίνακας υπαλλήλων περιέχει ορισμένα δεδομένα, αλλά ο περιορισμός ακεραιότητας αναφοράς δεν μπορεί να ικανοποιηθεί καθώς ο πίνακας τμήματος δεν έχει ακόμη δεδομένα.
είναι καλύτερο να εκτελέσετε δοκιμές συνδεσιμότητας σε υπολογιστή συνδεδεμένο σε ζωντανό δίκτυο.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`#sql-63_87`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)Για να έχουμε τον περιορισμό ΕΞΩΤΕΡΙΚΟΥ ΚΛΕΙΔΙΟΥ, θα πρέπει πρώτα να προσθέσουμε δεδομένα στον πίνακα Τμήματος. Ας εισαγάγουμε τις απαιτούμενες εγγραφές στον πίνακα Τμήματος.
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');Προσθέστε ξανά τον περιορισμό FOREIGN KEY εκτελώντας την ίδια δήλωση ALTER TABLE. Θα παρατηρήσετε ότι αυτή τη φορά, η εντολή είναι επιτυχής και ο πίνακας υπαλλήλων ενημερώνεται επιτυχώς για να έχει deptId ως FOREIGN KEY από τον πίνακα Department.
Απορρίπτοντας έναν περιορισμό ΞΕΝΟΥ ΚΛΕΙΔΙΟΥ
Παρόμοια με την προσθήκη ενός περιορισμού FOREIGN KEY, είναι επίσης δυνατό να αποθέσετε / διαγράψετε έναν υπάρχοντα περιορισμό FOREIGN KEY από έναν πίνακα.
Αυτό μπορεί να επιτευχθεί χρησιμοποιώντας την εντολή ALTER TABLE.
Σύνταξη:
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name}; Εδώ το «childTable» είναι το όνομα του πίνακα που έχει οριστεί περιορισμός ΞΕΝΩΝ ΚΛΕΙΔΙΟΥ, ενώ το «όνομα περιορισμού ξένου κλειδιού» είναι το όνομα / σύμβολο που χρησιμοποιήθηκε για τον ορισμό του ΚΛΕΙΔΙΟΥ ΕΞΩΤΕΡΙΚΟΥ.
Ας δούμε ένα παράδειγμα χρησιμοποιώντας τον πίνακα Υπάλληλος / Τμήμα. Για να αποθέσετε έναν περιορισμό που ονομάζεται 'depIdFk' από τον πίνακα υπαλλήλων, χρησιμοποιήστε την παρακάτω εντολή:
ALTER TABLE employee DROP FOREIGN KEY depIdFk;Συχνές Ερωτήσεις
Ε # 1) Πώς μπορώ να αλλάξω ξένα κλειδιά στο MySQL;
Απάντηση: Το FOREGIN KEY μπορεί να προστεθεί / αφαιρεθεί χρησιμοποιώντας την εντολή ALTER TABLE.
Για να αλλάξετε ή να προσθέσετε ένα νέο ΚΛΕΙΔΙ ΞΕΝΟ, μπορείτε να χρησιμοποιήσετε την εντολή ALTER και να ορίσετε τη στήλη ΠΛΗΚΤΡΟ ΞΕΝΩΝ και πίνακα αναφοράς που θα αναφέρεται από τον θυγατρικό πίνακα.
Q # 2) Πώς να ορίσετε πολλά ξένα κλειδιά στο MySQL;
Απάντηση: Ένας πίνακας στη MySQL μπορεί να έχει πολλά ΞΕΝΟ ΚΛΕΙΔΙΑ, τα οποία θα μπορούσαν να εξαρτώνται από τον ίδιο γονικό πίνακα ή διαφορετικούς γονικούς πίνακες.
Ας χρησιμοποιήσουμε τον πίνακα 'Υπάλληλος / Τμήμα' και προσθέτουμε το ΞΕΝΟ ΚΛΕΙΔΙ για το όνομα του Τμήματος καθώς και το Τμήμα του Τμήματος στον πίνακα Εργαζόμενος
Ανατρέξτε στις δηλώσεις ΔΗΜΙΟΥΡΓΙΑ και των δύο πινάκων όπως παρακάτω
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100) UNIQUE NOT NULL); ----xxxxx------xxxxx------xxxxx-------xxxxx------xxxxx CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, depName VARCHAR(100), CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT depNameFk FOREIGN KEY (depName) REFERENCES department(departmentName) ON UPDATE CASCADE ON DELETE CASCADE); Q # 3) Πώς να απενεργοποιήσετε τους περιορισμούς ξένου κλειδιού στη MySQL;
Απάντηση: Οι περιορισμοί ΞΕΝΟΥ ΚΛΕΙΔΙΟΥ συνήθως απαιτούνται όταν κάποιος προσπαθεί να κόψει έναν υπάρχοντα πίνακα στον οποίο γίνεται αναφορά. Για να το κάνετε αυτό, μπορείτε να χρησιμοποιήσετε την παρακάτω εντολή:
SET FOREIGN_KEY_CHECKS=0;Αυτό θα ορίσει μια μεταβλητή περιόδου λειτουργίας και θα απενεργοποιήσει προσωρινά το FOREIGN_KEY_CHECKS. Μετά από αυτήν τη ρύθμιση, μπορείτε να προχωρήσετε και να εκτελέσετε διαγραφές / περικοπές, κάτι που διαφορετικά δεν θα ήταν δυνατό.
σε τι χρησιμοποιείται το c ++
Αλλά βεβαιωθείτε ότι πρόκειται για προνόμιο διαχειριστή και θα πρέπει να χρησιμοποιείται με σύνεση.
Q # 4) Πώς μπορώ να βρω τις αναφορές ξένου κλειδιού για έναν πίνακα στη MySQL;
Απάντηση: Για να απαριθμήσετε όλους τους περιορισμούς που υπάρχουν, μπορείτε να χρησιμοποιήσετε τον πίνακα 'INNODB_FOREIGN_COLS' στην ενότητα 'INFORMATION_SCHEMA'.
Απλώς εκτελέστε την παρακάτω εντολή για να λάβετε όλες τις δηλώσεις ΕΞΩΤΕΡΙΚΟΥ ΚΛΕΙΔΙΟΥ για μια δεδομένη παρουσία διακομιστή MySQL.
| ταυτότητα | FOR_COL_NAME | REF_COL_NAME | POS |
|---|---|---|---|
| my_sql_foreign_key / depIdFk | deptId | τμήμα | 1 |
Ε # 5) Πρέπει η στήλη αναφοράς ως ΞΕΝΟ ΚΛΕΙΔΙ να είναι το πρωτεύον κλειδί στον πίνακα αναφοράς;
Απάντηση: Εξ ορισμού του ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ, απαιτείται η στήλη που αναφέρεται ως ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ να είναι το ΠΛΗΡΟΦΟΡΙΕΣ ΚΛΕΙΔΙ από τον πίνακα όπου γίνεται αναφορά.
Ωστόσο, με τις νεότερες εκδόσεις του MySQL και με τη μηχανή βάσης δεδομένων InnoDB, θα μπορούσατε επίσης να αναφέρετε μια στήλη που έχει ΕΞΩΤΕΡΙΚΟ ΚΛΕΙΔΙ που έχει ΜΟΝΑΔΙΚΟ περιορισμό και μπορεί να μην είναι απαραίτητα ΠΡΩΤΟΒΟΥΛΙΟ ΚΛΕΙΔΙ.
Q # 6) Δημιουργεί το FOREIGN KEY INDEX στο MySQL;
Απάντηση: Και για το πρωτεύον κλειδί και για το μοναδικό περιορισμό, η MySQL δημιουργεί αυτόματα ένα INDEX για τέτοιες στήλες.
Δεδομένου ότι ήδη γνωρίζουμε ότι οι αναφορές ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ μπορούν να εφαρμοστούν μόνο σε μια στήλη που είναι πρωτεύοντα κλειδιά ή σε στήλες που έχουν Μοναδικές τιμές, επομένως όλες οι στήλες που αναφέρονται ως ΚΛΕΙΔΙ ΕΞΩΤΕΡΙΚΟΥ έχουν ένα ευρετήριο που δημιουργήθηκε έναντι αυτών.
Για να δείτε το ευρετήριο σε έναν πίνακα, χρησιμοποιήστε την παρακάτω εντολή:
SHOW INDEX from {dbName.tableName};Έτσι, για παράδειγμα του Υπάλληλου / Τμήματος, είχαμε προσθέσει το deptId στο Employee ως ΞΕΝΟ ΚΛΕΙΔΙ από τον πίνακα του Τμήματος.
Ας δούμε τα δημιουργημένα ευρετήρια στους πίνακες υπαλλήλων και τμημάτων.
USE my_sql_foreign_key; SHOW INDEX from employee; | Τραπέζι | Μη_ μοναδικό | Όνομα_κλειδιού | Seq_in_index | Όνομα στήλης | Αντιπαραβολή | Καρδιοτητα | Υποτμήμα | Συσκευασμένα | Μηδενικό | Τύπος ευρετηρίου |
|---|---|---|---|---|---|---|---|---|---|---|
| υπάλληλος | 0 | ΠΡΩΤΑΡΧΙΚΟΣ | 1 | ταυτότητα | ΠΡΟΣ ΤΗΝ | 0 | ΜΗΔΕΝΙΚΟ | ΜΗΔΕΝΙΚΟ | BTREE | |
| υπάλληλος | 1 | depIdFk | 1 | deptId | ΠΡΟΣ ΤΗΝ | 0 | ΜΗΔΕΝΙΚΟ | ΜΗΔΕΝΙΚΟ | ΝΑΙ | BTREE |
Μπορείτε να δείτε 2 ευρετήρια - το ένα είναι το πρωτεύον κλειδί για τον πίνακα υπαλλήλων και ένα άλλο για το FOREIGN KEY depId που αναφέρεται από τον πίνακα τμημάτων.
SHOW INDEX from department;| Τραπέζι | Μη_ μοναδικό | Όνομα_κλειδιού | Seq_in_index | Όνομα στήλης | Αντιπαραβολή | Καρδιοτητα | Υποτμήμα | Συσκευασμένα | Μηδενικό | Τύπος ευρετηρίου |
|---|---|---|---|---|---|---|---|---|---|---|
| τμήμα | 0 | ΠΡΩΤΑΡΧΙΚΟΣ | 1 | τμήμα | ΠΡΟΣ ΤΗΝ | 0 | ΜΗΔΕΝΙΚΟ | ΜΗΔΕΝΙΚΟ | BTREE |
Εδώ μπορείτε να δείτε ότι για τον πίνακα τμημάτων, έχουμε μόνο 1 ευρετήριο για το πρωτεύον κλειδί (το οποίο αναφέρεται ως ΞΕΝΟ ΚΛΕΙΔΙ στον πίνακα υπαλλήλων).
Q # 7) Μπορεί το ΞΕΝΟ ΚΛΕΙΔΙ να είναι NULL στο MySQL;
Απάντηση: Ναι, είναι εντάξει να έχουμε NULL για τη στήλη που έχει εξάρτηση ΚΛΕΙΔΙ ΞΕΝΟΥ σε άλλο πίνακα. Αυτό αναφέρεται επίσης στο γεγονός ότι το NULL δεν είναι πραγματική τιμή, επομένως δεν ταιριάζει / συγκρίνεται με τιμές στον γονικό πίνακα.
συμπέρασμα
Σε αυτό το σεμινάριο, μάθαμε για διαφορετικές έννοιες που σχετίζονται με τη χρήση ΞΕΝΩΝ ΚΛΕΙΔΙΩΝ σε βάσεις δεδομένων MySQL.
Το FOREIGN KEY διευκολύνει τις ενημερώσεις και διαγράφει με τους κατάλληλους περιορισμούς, αλλά μερικές φορές η ύπαρξη πολλών τέτοιων σχέσεων μπορεί να κάνει ολόκληρη τη διαδικασία Εισαγωγής ή / και Διαγραφής αρκετά δυσκίνητη.
Μάθαμε πώς να δημιουργούμε FOREIGN KEYS και πώς μπορούμε να ενημερώσουμε και να αποθέσουμε ένα υπάρχον FOREIGN KEY από τον θυγατρικό πίνακα. Μάθαμε επίσης για διαφορετικές ενέργειες ακεραιότητας αναφοράς και πώς μπορούμε να επιτύχουμε διαφορετική συμπεριφορά χρησιμοποιώντας τις διαφορετικές διαθέσιμες επιλογές όπως το CASCADE, NO ACTION, SET NULL κ.λπ.
Συνιστώμενη ανάγνωση
- MySQL Δημιουργία εκπαιδευτικού πίνακα με παραδείγματα
- Εισαγωγή MySQL σε πίνακα - Εισαγωγή σύνταξης δήλωσης & παραδειγμάτων
- MySQL Δημιουργία εκπαιδευτικού προγράμματος με παραδείγματα κώδικα
- MySQL CONCAT και GROUP_CONCAT Λειτουργίες με παραδείγματα
- Εκμάθηση συναλλαγών MySQL με παραδείγματα προγραμματισμού
- MySQL UNION - Πλήρης οδηγός με παραδείγματα Union
- Πώς να κατεβάσετε το MySQL για Windows και Mac
- Διαφορά μεταξύ SQL Vs MySQL Vs SQL Server (με παραδείγματα)