Първичен ключ и външен ключ SQLite с примери

Разделите по-долу обясняват SQLite ограниченията подробно, започвайки с PRIMARY KEY и FOREIGN KEY, които дефинират и свързват таблиците, и обхващайки правилата NOT NULL, DEFAULT, UNIQUE и CHECK, които валидират данните във всяка колона.

SQLite Ограничения

Ограниченията на колоните налагат правила върху стойностите, вмъкнати в колона, за да се валидират данните. Тези ограничения се дефинират при създаването на таблица, в дефиницията на колоната. Те поддържат съхранените данни последователни и точни, като отхвърлят стойности, които нарушават зададените от вас правила, като например дубликати, нули или стойности, които не съществуват в свързана таблица.

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

- SQLite Ограничението NOT NULL не позволява на колоната да има null стойност:

ColumnName INTEGER  NOT NULL;

Ограничение ПО ПОДРАЗБИРАНЕ

С SQLite Ограничение ПО СТАНДАРТНО, ако не вмъкнете никаква стойност в колона, вместо това се вмъква стойността по подразбиране.

Например:

ColumnName INTEGER DEFAULT 0;

Ако напишете оператор за вмъкване и не укажете никаква стойност за тази колона, колоната ще има стойност 0.

УНИКАЛНО ограничение

- SQLite Ограничението UNIQUE предотвратява дублирането на стойности сред всички стойности в колоната.

Например:

EmployeeId INTEGER NOT NULL UNIQUE;

Това налага стойността „EmployeeId“ да бъде уникална; не се допускат дублирани стойности. Обърнете внимание, че това се отнася само за стойностите на колоната „EmployeeId“.

ПРОВЕРЕТЕ ограничение

- SQLite Ограничението CHECK задава условие за проверка на вмъкната стойност. Ако стойността не отговаря на условието, тя няма да бъде вмъкната.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

Не можете да въведете стойност по-малка от 10 в колоната „Количество“.

SQLite Чужд ключ

- 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 INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

Двете оператори трябва да вмъкнат два отдела в таблицата „Отдели“. Можете да потвърдите, че двете стойности са вмъкнати, като изпълните след това заявката „SELECT * FROM Отдели“:

След това опитайте да вмъкнете нов студент с departmentId, който не съществува в таблицата Departments:

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

Редът няма да бъде вмъкнат и ще получите грешка, която гласи: Ограничението FOREIGN KEY е неуспешно.

Разлика между първичен ключ и външен ключ в SQLite

Първичните и външните ключове помагат за поддържане на целостта на данните, но играят различни роли. Първичният ключ идентифицира редове в една таблица, докато външният ключ свързва редове в две свързани таблици. Таблицата по-долу обобщава основните разлики.

В примера със Студенти и Отдели, DepartmentId е основният ключ на таблицата Отдели и външен ключ в таблицата Студенти, който свързва всеки студент с валиден отдел.

SQLite Съставен първичен ключ

Съставният първичен ключ е първичен ключ, съставен от две или повече колони. Използва се, когато никоя колона не е уникална сама по себе си, но комбинацията от колони е уникална за всеки ред. SQLite третира комбинираните стойности като един ключ.

Например, таблица за записване може да позволи един и същ студент в много курсове и един и същ курс за много студенти, но всяка двойка студент-курс трябва да се появява само веднъж:

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

Тук нито StudentId, нито CourseId са уникални поотделно, но двойката (StudentId, CourseId) е уникална, така че един и същ студент не може да бъде записан в един и същ курс два пъти. Обърнете внимание на следните точки, когато използвате съставен ключ:

• Използвайте съставен ключ, когато една колона не може еднозначно да идентифицира ред.
• Всяка колона в съставния ключ следва правилата за първичен ключ, така че комбинираната стойност трябва да е уникална и да не е null.
• Съставният ключ се записва като отделна клауза PRIMARY KEY на ниво таблица, а не в дефиниция на една колона.

SQLite Действия с външен ключ: ON DELETE и ON UPDATE

Външен ключ може също да контролира какво се случва с дъщерните редове, когато родителският ред, към който се позовават, бъде изтрит или актуализиран. Тези действия за препращане се добавят с клаузите ON DELETE и ON UPDATE, когато дефинирате външния ключ. SQLite подкрепя пет действия:

• НЯМА ДЕЙСТВИЕ — действието по подразбиране, което повдига грешка, ако дъщерните редове все още препращат към родителския.
• ОГРАНИЧАВАНЕ — предотвратява незабавното изтриване или актуализиране, преди да се изпълни каквато и да е друга промяна.
• SET NULL — задава дъщерната колона с външен ключ на null.
• ЗАДЪЛЖИТЕЛНО — задава колоната с външен ключ на дъщерния елемент на декларираната ѝ стойност по подразбиране.
• КАСКАДА — прилага същата промяна към дъщерните редове, така че изтриването на родител изтрива и неговите деца.

Примерът по-долу пресъздава таблицата „Студенти“, така че изтриването на отдел автоматично изтрива неговите студенти, а актуализирането на идентификатора на отдел актуализира съответстващите студенти:

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, но не ги прилага.

Въпроси и Отговори