Урок 3 Часть вторая. Primary/Foreign Keys

Database Developer SQL Essential
1

Видеоурок, на котором базируется данный пост - Основы DDL. Для лучшего усвоения информации и более подробного изучения SQL настоятельно рекоммендем Вам просмотреть видеокурс SQL Essential.

Целостность и противоречивость данных в БД

Давайте рассмотрим вопросы целостности и непротиворечивости баз данных.

По-сути наши базы данных полностью открыты для пользователей. Единственное ограничение, которое на данный момент ограничивает пользователя от ввода противоречивых данных - это типы данных, которыми мы закрываем поля нашей БД (если тип, например, INT, то ввести в данное поле какую-то строку для пользователя будет невозможно).

Но при этом пользователь запросто может написать, например, свою фамилию в поле для имени, или номер телефона в поле для почтового ящика. Подобные ошибки нарушают логику в нашей базе данных.

Так как же нам ограничить данные от подобных ошибок? Процесс ограничения данных называется обеспечением целостности данных.

Согласно определению, целостность БД - это свойство БД, которое обеспечивает корректность и непротиворечивость данных в любой момент времени. Поянтное дело, что целостность БД не гарантирует никакой достоверности вводимой информации, но по крайней мере отсевает заведомо нереальные значения.

Правдоподобность вводимых данных в БД обеспечивается ограничениями на вводимые данные (для аналогии, к примеру, C# - это своего рода регулярные выражения).

Существует 3 вида целостности баз данных: доменная целостность, целостность сущностей и ссылочная целостность. Подробнее о них вы можете узнать в видеоуроке. Я же просто вкратце перечислю ограничения, которые обеспечивают эти виды целостности:

  • Доменная целостность: CHECK(ограничение проверки), DEFAULT(запись значения по-умолчанию).
  • Целостность сущностей: Primary Key(первичный ключ), UNIQUE(уникальность).
  • Ссылочная целостность: Foreign Key(внешний ключ).

Итак, давайте же рассмотрим на примерах что такое Primary Key, Foreign Key и зачем они нужны.

Primary Key

Primary Key - (он же - первичный ключ) - предоставляет ссылку для связи с другими таблицами и создаёт ограничение уникальности для столбца, на котором задаётся.

Первичный ключ имеет пару особенностей, которые необходимо знать для его успешного применения:

  • Primary Key может быть исключительно один. Несколько первичных ключей сделать невозможно, т.к. он, своего рода, уникален.
  • Первичный ключ может быть простым и составным, со всеми вытекающими последствиями)

Давайте посмотрим на разницу между простым и составным первичными ключами на примере создания таблицы Customers в нашей базе данных. После создания таблицы с первичными ключами мы будем удалять данную таблицу.

Простой Primary Key:

create table Customers
(
	idField int IDENTITY(1,2), 
	CustomerName char(20) not null,
	CustomerSurname char(25) null,
	CustomerPhone char(10) not null primary key, -- на столбце CustomerPhone задаём простой первичный ключ.
	CustomerEmail char(45) default 'unknown',
	RegistrationTime date default getdate()
)

drop table Customers;
go

Как мы видим по примеру, простой первичный ключ задаётся очень просто, путём простого применения ключевых слов PRIMARY KEY.

Составной Primary Key

create table Customers -- пример составного первичного ключа.
(
	idField int IDENTITY(1,2), 
	CustomerName char(20) default 'unknown',
	CustomerSurname char(25) default 'unknown',
	CustomerPhone char(12) not null,
	CustomerEmail char(45) not null,
	RegistrationTime date null,
	primary key(CustomerPhone, CustomerEmail) -- пример составного первичного ключа. 
)

drop table Customers
go

Как видим, применение составного первичного ключа гарантирует уникальность только пары значений (!!!), тобишь правдивость будет подтверждена только если оба значения в одно время будут уникальными.

Итак, мы разобрались, что такое и для чего нужен первичный ключ. Давайте теперь создадим таблицу, используя первичный ключ:

create table Customers
(
	PersonalID int identity not null primary key, 
	CustomerName char(20) not null,
	CustomerSurname char(25) default 'unknown',
	CustomerPhone char(12) not null,
	CustomerEmail char(45) null
)

Мы создали таблицу Customers (тобишь, пользователей). Теперь давайте создадим ещё одну таблицу Orders, которая будет вмещать в себе заказы наших пользователей.

Тут возникает вопрос: как же нам привязать одну таблицу к другой?

Вот для этого нам необходимо использовать внешний ключ, он же - Foreign Key.

2

Foreign Key

Foreign Key нужен для того, что бы предоставить определённую ссылку для связи между двумя таблицами.

Создадим таблицу Orders, которая будет отображать наши заказы, и привяжем поле из таблицы Orders к полю из таблицы Customers через Foreign Key, что б у каждого пользователя у нас могло быть несколько заказов:

create table Orders
(
	OrderID int identity not null primary key,
	PersonalID int not null foreign key references Customers(PersonalID), -- привязка поля данной таблицы к полю другой таблицы.
	OrderDate date not null,
	Goods varchar(30) not null
)
go

Всё. Теперь у нас есть 2, связанные ключами, таблицы. Обращаю ваше внимание, что данные таблицы теперь нельзя просто так удалить, используя оператор DROP, т.к. теперь они связаны ссылками.

Если теперь вам понадобится удалить данные таблицы - необходимо или, используя T-SQL, удалить ссылки, а потом удалить таблицы через DROP, или используя визуальные инструменты сразу удалить обе таблицы.

Теперь давайте создадим несколько пользователей и несколько товаров, принадлежащих нашим пользователям:

insert Customers
values
('Vasya', 'Petrenko', '380*********', null),
('Petya', 'Petrenko', '380*********', null)
go

insert Orders (PersonalID, OrderDate, Goods)

values

(1, getdate(), 'goods1'),

(2, getdate(), 'goods2'),

(1, getdate(), 'goods3')

go

Теперь давайте посмотрим, что у нас получилось:

select * from Orders
go

Для пущей уверенности что таблицы у Вас связались успешно - попробуйте используя оператор SELECT вывести все данные из таблиц.