Κύριο κλειδί και Ξένο κλειδί in SQLite με παραδείγματα

Οι παρακάτω ενότητες εξηγούν SQLite περιορισμοί λεπτομερώς, ξεκινώντας με το ΠΡΩΤΕΥΟΝ ΚΛΕΙΔΙ και το ΞΕΝΟ ΚΛΕΙΔΙ που ορίζουν και συνδέουν πίνακες, και καλύπτοντας τους κανόνες NOT NULL, DEFAULT, UNIQUE και CHECK που επικυρώνουν τα δεδομένα σε κάθε στήλη.

SQLite Περιορισμοί

Οι περιορισμοί στηλών επιβάλλουν κανόνες στις τιμές που εισάγονται σε μια στήλη, προκειμένου να επικυρωθούν τα δεδομένα. Αυτοί οι περιορισμοί ορίζονται κατά τη δημιουργία ενός πίνακα, μέσα στον ορισμό της στήλης. Διατηρούν τα αποθηκευμένα δεδομένα συνεπή και ακριβή, απορρίπτοντας τιμές που παραβιάζουν τους κανόνες που ορίζετε, όπως διπλότυπα, null ή τιμές που δεν υπάρχουν σε έναν σχετικό πίνακα.

SQLite Πρωτεύων κλειδί

Όλες οι τιμές σε μια στήλη πρωτεύοντος κλειδιού πρέπει να είναι μοναδικές και όχι null. Το πρωτεύον κλειδί προσδιορίζει μοναδικά κάθε γραμμή στον πίνακα.

Το πρωτεύον κλειδί μπορεί να εφαρμοστεί μόνο σε μία στήλη ή σε έναν συνδυασμό στηλών. Στην τελευταία περίπτωση, ο συνδυασμός των τιμών των στηλών θα πρέπει να είναι μοναδικός για όλες τις γραμμές του πίνακα.

Σύνταξη:

Υπάρχουν διάφοροι τρόποι για να ορίσετε ένα πρωτεύον κλειδί σε έναν πίνακα:

Στον ίδιο τον ορισμό της στήλης:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Ως ξεχωριστός ορισμός:

PRIMARY KEY(ColumnName);

Για να δημιουργήσετε έναν συνδυασμό στηλών ως πρωτεύον κλειδί:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Περιορισμοί NOT NULL, DEFAULT, UNIQUE και CHECK

Εκτός από το πρωτεύον κλειδί, SQLite παρέχει αρκετούς περιορισμούς στηλών που επικυρώνουν τις τιμές που εισάγονται σε έναν πίνακα. Οι περιορισμοί NOT NULL, DEFAULT, UNIQUE και CHECK ορίζονται ο καθένας στον ορισμό της στήλης και ο καθένας από αυτούς επιβάλλει έναν συγκεκριμένο κανόνα στη στήλη. Τύπος δεδομένων και αξίες.

NOT NULL Περιορισμός

The SQLite Ο περιορισμός NOT NULL εμποδίζει μια στήλη να έχει μια τιμή null:

ColumnName INTEGER  NOT NULL;

Προεπιλεγμένος περιορισμός

Με την SQLite Με τον περιορισμό DEFAULT, εάν δεν εισαγάγετε καμία τιμή σε μια στήλη, εισάγεται η προεπιλεγμένη τιμή.

Για παράδειγμα:

ColumnName INTEGER DEFAULT 0;

Εάν γράψετε μια εντολή insert και δεν καθορίσετε καμία τιμή για αυτήν τη στήλη, η στήλη θα έχει την τιμή 0.

Μοναδικός περιορισμός

The SQLite Ο περιορισμός UNIQUE αποτρέπει τις διπλότυπες τιμές μεταξύ όλων των τιμών της στήλης.

Για παράδειγμα:

EmployeeId INTEGER NOT NULL UNIQUE;

Αυτό επιβάλλει στην τιμή "EmployeeId" να είναι μοναδική. Δεν επιτρέπονται διπλότυπες τιμές. Σημειώστε ότι αυτό ισχύει μόνο για τις τιμές της στήλης "EmployeeId".

Ελέγξτε τον περιορισμό

The SQLite Ο περιορισμός CHECK ορίζει μια συνθήκη για τον έλεγχο μιας εισαγόμενης τιμής. Εάν η τιμή δεν ταιριάζει με τη συνθήκη, δεν θα εισαχθεί.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Δεν μπορείτε να εισαγάγετε τιμή μικρότερη από 10 στη στήλη "Ποσότητα".

SQLite Ξένο κλειδί

The SQLite Ένα ξένο κλειδί είναι ένας περιορισμός που επαληθεύει την ύπαρξη μιας τιμής που υπάρχει σε έναν πίνακα σε έναν άλλο πίνακα που έχει μια σχέση με τον πρώτο πίνακα όπου ορίζεται το ξένο κλειδί.

Κατά την εργασία με πολλούς πίνακες, υπάρχουν περιπτώσεις όπου δύο πίνακες σχετίζονται μεταξύ τους μέσω μίας κοινής στήλης. Εάν θέλετε να διασφαλίσετε ότι η τιμή που εισάγεται σε έναν από αυτούς πρέπει να υπάρχει στη στήλη του άλλου πίνακα, τότε θα πρέπει να χρησιμοποιήσετε έναν περιορισμό ξένου κλειδιού στην κοινή στήλη.

Σε αυτήν την περίπτωση, όταν προσπαθείτε να εισαγάγετε μια τιμή σε αυτήν τη στήλη, το ξένο κλειδί θα διασφαλίσει ότι η εισαγόμενη τιμή υπάρχει στη στήλη του πίνακα αναφοράς.

Σημειώστε ότι οι περιορισμοί ξένου κλειδιού δεν είναι ενεργοποιημένοι από προεπιλογή στο SQLiteΠρέπει πρώτα να τα ενεργοποιήσετε εκτελώντας την ακόλουθη εντολή:

PRAGMA foreign_keys = ON;

Εισήχθησαν περιορισμοί εξωτερικού κλειδιού SQLite ξεκινώντας από την έκδοση 3.6.19.

Παράδειγμα SQLite Ξένο κλειδί

Ας υποθέσουμε ότι έχουμε δύο πίνακες: Φοιτητές και Τμήματα.

Ο πίνακας "Φοιτητές" περιέχει μια λίστα φοιτητών και ο πίνακας "Τμήματα" περιέχει μια λίστα των τμημάτων. Κάθε φοιτητής ανήκει σε ένα τμήμα, δηλαδή, κάθε φοιτητής έχει μια στήλη "departmentId".

Τώρα θα δούμε πώς ο περιορισμός ξένου κλειδιού μπορεί να είναι χρήσιμος για να διασφαλιστεί ότι η τιμή του αναγνωριστικού τμήματος στον πίνακα Φοιτητές πρέπει να υπάρχει στον πίνακα Τμήματα.

Έτσι, αν δημιουργήσουμε έναν περιορισμό ξένου κλειδιού στο DepartmentId στον πίνακα Students, κάθε εισαγόμενο departmentId πρέπει να υπάρχει στον πίνακα Departments.

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

Για να ελέγξουμε πώς οι περιορισμοί ξένου κλειδιού μπορούν να αποτρέψουν την εισαγωγή ενός απροσδιόριστου στοιχείου ή τιμής σε έναν πίνακα που έχει σχέση με έναν άλλο πίνακα, θα εξετάσουμε το ακόλουθο παράδειγμα.

Σε αυτό το παράδειγμα, ο πίνακας Τμήματα έχει μια σχέση ξένου κλειδιού με τον πίνακα Φοιτητές, επομένως οποιαδήποτε τιμή departmentId που εισάγεται στον πίνακα Φοιτητές πρέπει να υπάρχει στον πίνακα Τμήματα. Εάν προσπαθήσετε να εισαγάγετε μια τιμή departmentId που δεν υπάρχει στον πίνακα Τμήματα, ο περιορισμός ξένου κλειδιού θα σας εμποδίσει να το κάνετε αυτό.

Ας εισαγάγουμε δύο τμήματα, το «Πληροφορική» και το «Τέχνες», στον πίνακα Τμημάτων με τα ακόλουθα Ερωτήματα INSERT:

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

Οι δύο δηλώσεις θα πρέπει να εισάγουν δύο τμήματα στον πίνακα Τμήματα. Μπορείτε να επιβεβαιώσετε ότι οι δύο τιμές εισήχθησαν εκτελώντας στη συνέχεια το ερώτημα "SELECT * FROM Τμήματα":

Στη συνέχεια, προσπαθήστε να εισαγάγετε έναν νέο φοιτητή με αναγνωριστικό τμήματος που δεν υπάρχει στον πίνακα Τμήματα:

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

Η γραμμή δεν θα εισαχθεί και θα εμφανιστεί ένα σφάλμα που θα αναφέρει: Ο περιορισμός ΕΞΩΤΕΡΙΚΟΥ ΚΛΕΙΔΙΟΥ απέτυχε.

Διαφορά μεταξύ πρωτεύοντος κλειδιού και ξένου κλειδιού στο SQLite

Τα πρωτεύοντα και τα ξένα κλειδιά συμβάλλουν και τα δύο στη διατήρηση της ακεραιότητας των δεδομένων, αλλά παίζουν διαφορετικούς ρόλους. Ένα πρωτεύον κλειδί προσδιορίζει γραμμές μέσα σε έναν μόνο πίνακα, ενώ ένα ξένο κλειδί συνδέει γραμμές μεταξύ δύο σχετικών πινάκων. Ο παρακάτω πίνακας συνοψίζει τις κύριες διαφορές.

Στο παράδειγμα Φοιτητές και Τμήματα, το DepartmentId είναι το πρωτεύον κλειδί του πίνακα Departments και ένα ξένο κλειδί στον πίνακα Students, το οποίο συνδέει κάθε φοιτητή με ένα έγκυρο τμήμα.

SQLite Σύνθετο Πρωτεύον Κλειδί

Ένα σύνθετο πρωτεύον κλειδί είναι ένα πρωτεύον κλειδί που αποτελείται από δύο ή περισσότερες στήλες. Χρησιμοποιείται όταν καμία μεμονωμένη στήλη δεν είναι μοναδική από μόνη της, αλλά ο συνδυασμός στηλών είναι μοναδικός για κάθε γραμμή. SQLite αντιμετωπίζει τις συνδυασμένες τιμές ως ένα κλειδί.

Για παράδειγμα, ένας πίνακας εγγραφών μπορεί να επιτρέπει στον ίδιο φοιτητή πολλά μαθήματα και το ίδιο μάθημα για πολλούς φοιτητές, ωστόσο κάθε ζεύγος φοιτητή-μαθήματος θα πρέπει να εμφανίζεται μόνο μία φορά:

CREATE TABLE Enrollments (
	StudentId INTEGER NOT NULL,
	CourseId INTEGER NOT NULL,
	Grade TEXT,
	PRIMARY KEY (StudentId, CourseId)
);

Εδώ ούτε το StudentId ούτε το CourseId είναι μοναδικά από μόνα τους, αλλά το ζεύγος (StudentId, CourseId) είναι μοναδικό, επομένως ο ίδιος φοιτητής δεν μπορεί να εγγραφεί στο ίδιο μάθημα δύο φορές. Σημειώστε τα ακόλουθα σημεία όταν χρησιμοποιείτε ένα σύνθετο κλειδί:

• Χρησιμοποιήστε ένα σύνθετο κλειδί όταν μια μεμονωμένη στήλη δεν μπορεί να προσδιορίσει με μοναδικό τρόπο μια γραμμή.
• Κάθε στήλη στο σύνθετο κλειδί ακολουθεί τους κανόνες του πρωτεύοντος κλειδιού, επομένως η συνδυασμένη τιμή πρέπει να είναι μοναδική και όχι null.
• Ένα σύνθετο κλειδί γράφεται ως ξεχωριστός όρος ΠΡΩΤΕΥΟΝΤΟΣ ΚΛΕΙΔΙ σε επίπεδο πίνακα, όχι μέσα σε έναν ορισμό μίας μόνο στήλης.

SQLite Ξένες βασικές ενέργειες: ΚΑΤΑ ΤΗ ΔΙΑΓΡΑΦΗ και ΚΑΤΑ ΤΗΝ ΕΝΗΜΕΡΩΣΗ

Ένα ξένο κλειδί μπορεί επίσης να ελέγξει τι συμβαίνει στις θυγατρικές γραμμές όταν η γονική γραμμή στην οποία αναφέρεται διαγράφεται ή ενημερώνεται. Αυτές οι ενέργειες αναφοράς προστίθενται με τους όρους ON DELETE και ON UPDATE όταν ορίζετε το ξένο κλειδί. SQLite υποστηρίζει πέντε δράσεις:

• ΚΑΜΙΑ ΔΡΑΣΗ — η προεπιλεγμένη ενέργεια, η οποία εμφανίζει σφάλμα εάν οι θυγατρικές γραμμές εξακολουθούν να αναφέρονται στη γονική γραμμή.
• ΠΕΡΙΟΡΙΖΩ — αποτρέπει την άμεση διαγραφή ή ενημέρωση, πριν από την εκτέλεση οποιασδήποτε άλλης αλλαγής.
• SET NULL — ορίζει τη στήλη του θυγατρικού ξένου κλειδιού σε null.
• ΡΥΘΜΙΣΤΕ ΤΟ DEFAULT — ορίζει τη στήλη του θυγατρικού ξένου κλειδιού στην δηλωμένη προεπιλεγμένη τιμή της.
• CASCADE — εφαρμόζεται η ίδια αλλαγή στις θυγατρικές σειρές, επομένως η διαγραφή ενός γονικού στοιχείου διαγράφει και τις θυγατρικές του σειρές.

Το παρακάτω παράδειγμα αναδημιουργεί τον πίνακα Φοιτητές έτσι ώστε η διαγραφή ενός τμήματος να διαγράφει αυτόματα τους φοιτητές του και η ενημέρωση ενός αναγνωριστικού τμήματος ενημερώνει τους φοιτητές που ταιριάζουν:

CREATE TABLE Students (
	StudentId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	StudentName NVARCHAR(50) NULL,
	DepartmentId INTEGER NOT NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

Να θυμάστε ότι οι ενέργειες αναφοράς εκτελούνται μόνο όταν είναι ενεργοποιημένη η υποστήριξη ξένων κλειδιών, επομένως εκτελέστε την PRAGMA foreign_keys = ON στην αρχή κάθε σύνδεσης. Χωρίς αυτήν, SQLite αναλύει τις ρήτρες ON DELETE και ON UPDATE, αλλά δεν τις επιβάλλει.

Συχνές Ερωτήσεις