Klucz podstawowy i klucz obcy SQLite z przykładami

Poniższe sekcje wyjaśniają SQLite ograniczenia szczegółowo opisane, zaczynając od KLUCZA PODSTAWOWEGO i KLUCZA OBCEGO, które definiują i łączą tabele, a kończąc na regułach NOT NULL, DEFAULT, UNIQUE i CHECK, które weryfikują dane w każdej kolumnie.

SQLite ograniczenia

Ograniczenia kolumnowe wymuszają reguły dotyczące wartości wstawianych do kolumny w celu walidacji danych. Ograniczenia te są definiowane podczas tworzenia tabeli, w definicji kolumny. Zapewniają one spójność i dokładność przechowywanych danych poprzez odrzucanie wartości, które naruszają ustawione reguły, takich jak duplikaty, wartości null lub wartości, które nie występują w powiązanej tabeli.

SQLite Główny klucz

Wszystkie wartości w kolumnie klucza podstawowego muszą być unikalne i nie mogą być nullem. Klucz podstawowy jednoznacznie identyfikuje każdy wiersz w tabeli.

Klucz podstawowy można zastosować tylko do jednej kolumny lub do kombinacji kolumn. W drugim przypadku kombinacja wartości kolumn powinna być unikatowa dla wszystkich wierszy tabeli.

Składnia:

Istnieje kilka różnych sposobów definiowania klucza podstawowego w tabeli:

W samej definicji kolumny:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Jako osobna definicja:

PRIMARY KEY(ColumnName);

Aby utworzyć kombinację kolumn jako klucz podstawowy:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Ograniczenia NOT NULL, DEFAULT, UNIQUE i CHECK

Oprócz klucza podstawowego, SQLite Zawiera kilka ograniczeń kolumnowych, które weryfikują wartości wprowadzone do tabeli. Ograniczenia NOT NULL, DEFAULT, UNIQUE i CHECK są zdefiniowane w definicji kolumny, a każde z nich wymusza określoną regułę dla kolumny. typ danych i wartości.

NIE NULL Ograniczenie

SQLite Ograniczenie NOT NULL zapobiega temu, aby kolumna miała wartość null:

ColumnName INTEGER  NOT NULL;

Domyślne ograniczenie

Z SQLite Ograniczenie DEFAULT: jeśli w kolumnie nie zostanie wstawiona żadna wartość, zamiast niej zostanie wstawiona wartość domyślna.

Na przykład:

ColumnName INTEGER DEFAULT 0;

Jeśli napiszesz polecenie INSERT i nie podasz żadnej wartości dla danej kolumny, kolumna będzie miała wartość 0.

UNIKALNE ograniczenie

SQLite Ograniczenie UNIQUE zapobiega duplikowaniu wartości wśród wszystkich wartości kolumny.

Na przykład:

EmployeeId INTEGER NOT NULL UNIQUE;

Wymusza to unikalność wartości „EmployeeId”; duplikaty wartości są niedozwolone. Należy pamiętać, że dotyczy to tylko wartości kolumny „EmployeeId”.

SPRAWDŹ ograniczenie

SQLite Ograniczenie CHECK ustawia warunek sprawdzania wstawionej wartości. Jeśli wartość nie spełnia warunku, nie zostanie wstawiona.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

W kolumnie „Ilość” nie można wpisać wartości mniejszej niż 10.

SQLite klucz obcy

SQLite klucz obcy to ograniczenie weryfikujące istnienie wartości obecnej w jednej tabeli w innej tabeli, która ma relację z pierwszą tabelą, w której zdefiniowano klucz obcy.

Podczas pracy z wieloma tabelami zdarzają się sytuacje, gdy dwie tabele są ze sobą powiązane poprzez jedną wspólną kolumnę. Aby mieć pewność, że wartość wprowadzona do jednej z nich musi znajdować się w kolumnie drugiej tabeli, należy zastosować ograniczenie klucza obcego dla wspólnej kolumny.

W takim przypadku, gdy próbujesz wstawić wartość do tej kolumny, klucz obcy sprawdzi, czy wstawiona wartość istnieje w kolumnie tabeli, do której się odwołujesz.

Należy pamiętać, że ograniczenia klucza obcego nie są domyślnie włączone w SQLiteNajpierw musisz je włączyć, uruchamiając następujące polecenie:

PRAGMA foreign_keys = ON;

Wprowadzono ograniczenia klucza obcego SQLite począwszy od wersji 3.6.19.

Przykład SQLite klucz obcy

Załóżmy, że mamy dwie tabele: Studenci i Wydziały.

Tabela „Studenci” zawiera listę studentów, a tabela „Departamenty” listę wydziałów. Każdy student należy do wydziału, co oznacza, że ​​każdy student ma kolumnę „departmentId”.

Teraz zobaczymy, w jaki sposób ograniczenie klucza obcego może być pomocne w zapewnieniu, że wartość identyfikatora wydziału w tabeli Students musi istnieć w tabeli Departments.

Jeśli zatem utworzymy ograniczenie klucza obcego dla DepartmentId w tabeli Students, każdy wstawiony identyfikator departmentId musi znajdować się w tabeli 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)
);

Aby sprawdzić, w jaki sposób ograniczenia klucza obcego mogą zapobiec wstawieniu niezdefiniowanego elementu lub wartości do tabeli, która jest powiązana z inną tabelą, przyjrzymy się następującemu przykładowi.

W tym przykładzie tabela „Departments” ma relację klucza obcego z tabelą „Students”, więc każda wartość „departmentId” wstawiona do tabeli „Students” musi istnieć w tabeli „Departments”. Jeśli spróbujesz wstawić wartość „departmentId”, która nie istnieje w tabeli „Departments”, ograniczenie klucza obcego uniemożliwi Ci to.

Wstawmy dwa działy: „IT” i „Sztuka” do tabeli Działy z następującym Zapytania INSERT:

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

Te dwa polecenia powinny wstawić dwa działy do ​​tabeli „Departments”. Możesz potwierdzić, że te dwie wartości zostały wstawione, uruchamiając później zapytanie „SELECT * FROM Departments”:

Następnie spróbuj wstawić nowego studenta z identyfikatorem wydziału, który nie istnieje w tabeli Wydziały:

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

Wiersz nie zostanie wstawiony i pojawi się komunikat o błędzie: Ograniczenie klucza obcego nie powiodło się.

Różnica między kluczem podstawowym a kluczem obcym w SQLite

Klucze podstawowe i klucze obce pomagają zachować integralność danych, ale pełnią różne role. Klucz podstawowy identyfikuje wiersze w jednej tabeli, podczas gdy klucz obcy łączy wiersze w dwóch powiązanych tabelach. Poniższa tabela podsumowuje główne różnice.

W przykładzie Studenci i Działy DepartmentId jest kluczem podstawowym tabeli Działy i kluczem obcym w tabeli Studenci, który wiąże każdego studenta z prawidłowym działem.

SQLite Klucz podstawowy złożony

Klucz podstawowy złożony to klucz podstawowy składający się z dwóch lub więcej kolumn. Jest stosowany, gdy żadna pojedyncza kolumna nie jest unikatowa sama w sobie, ale kombinacja kolumn jest unikatowa dla każdego wiersza. SQLite traktuje połączone wartości jako jeden klucz.

Na przykład tabela zapisów może zezwalać temu samemu studentowi na udział w wielu kursach i na udział w tym samym kursie wielu studentom, ale każda para student-kurs powinna pojawić się tylko raz:

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

W tym przypadku ani StudentId, ani CourseId nie są unikalne same w sobie, ale para (StudentId, CourseId) jest unikalna, więc ten sam student nie może zapisać się na ten sam kurs dwa razy. Pamiętaj o następujących kwestiach, używając klucza złożonego:

• Użyj klucza złożonego, gdy pojedyncza kolumna nie może jednoznacznie zidentyfikować wiersza.
• Każda kolumna w kluczu złożonym podlega regułom klucza podstawowego, więc łączna wartość musi być unikatowa i nie może być wartością null.
• Klucz złożony zapisuje się jako osobną klauzulę PRIMARY KEY na poziomie tabeli, a nie wewnątrz definicji pojedynczej kolumny.

SQLite Akcje klucza obcego: ON DELETE i ON UPDATE

Klucz obcy może również kontrolować, co dzieje się z wierszami podrzędnymi, gdy wiersz nadrzędny, do którego się odwołują, zostanie usunięty lub zaktualizowany. Te akcje referencyjne są dodawane za pomocą klauzul ON DELETE i ON UPDATE podczas definiowania klucza obcego. SQLite wspiera pięć działań:

• BRAK DZIAŁANIA — domyślna akcja, która zgłasza błąd, jeśli wiersze podrzędne nadal odwołują się do wiersza nadrzędnego.
• OGRANICZAĆ — zapobiega natychmiastowemu usunięciu lub aktualizacji, przed wprowadzeniem jakiejkolwiek innej zmiany.
• USTAW NULL — ustawia kolumnę podrzędnego klucza obcego na null.
• USTAW DOMYŚLNIE — ustawia kolumnę podrzędnego klucza obcego na zadeklarowaną wartość domyślną.
• CASCADE — stosuje tę samą zmianę do wierszy podrzędnych, więc usunięcie elementu nadrzędnego powoduje usunięcie jego elementów podrzędnych.

Poniższy przykład odtwarza tabelę Studenci, tak aby usunięcie wydziału powodowało automatyczne usunięcie studentów, a aktualizacja identyfikatora wydziału aktualizowała pasujących studentów:

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
);

Pamiętaj, że akcje referencyjne działają tylko wtedy, gdy obsługa kluczy obcych jest włączona, dlatego uruchamiaj PRAGMA foreign_keys = ON na początku każdego połączenia. Bez niej SQLite analizuje klauzule ON DELETE i ON UPDATE, ale ich nie wymusza.

FAQ