Ограничения целостности

1.

2.

ЧТОБЫ ИНФОРМАЦИЯ, ХРАНЯЩАЯСЯ В БД, БЫЛА
ОДНОЗНАЧНОЙ И НЕПРОТИВОРЕЧИВОЙ, В РЕЛЯЦИОННОЙ
МОДЕЛИ УСТАНАВЛИВАЮТСЯ НЕКОТОРЫЕ ОГРАНИЧИТЕЛЬНЫЕ
УСЛОВИЯ - ПРАВИЛА, ОПРЕДЕЛЯЮЩИЕ ВОЗМОЖНЫЕ ЗНАЧЕНИЯ
ДАННЫХ И ОБЕСПЕЧИВАЮЩИЕ ЛОГИЧЕСКУЮ ОСНОВУ ДЛЯ
ПОДДЕРЖАНИЯ КОРРЕКТНЫХ ЗНАЧЕНИЙ.

3.

ТИПЫ ОГРАНИЧЕНИЙ ЦЕЛОСТНОСТИ ДАННЫХ
Обязательные
данные
Ограничения для
доменов полей
• Некоторые поля
не могут иметь
пустого
значения
• Каждое поле
имеет свой
домен,
представляющ
ий собой набор
его допустимых
значений
Корпоративные
ограничения
целостности
Целостность
сущностей
• правила поддержки
целостности
данных,
определяемые
пользователями,
принятые на
предприятии или
администраторами
баз данных - бизнесправила.
• касается
первичных
ключей таблиц
• в базовой таблице
ни одно поле
первичного ключа
не может
содержать
значения NULL
Ссылочная
целостность
• касается
внешних
ключей

4.

ТАБЛИЦЫ С ОГРАНИЧЕНИЯМИ В
СТАНДАРТЕ ЯЗЫКА

5.

ТИПЫ ОГРАНИЧЕНИЙ ЦЕЛОСТНОСТИ ДАННЫХ
Обязательные
данные
Ограничения для
доменов полей
• Использование
NOT NULL в
спецификации
поля
• домен некоторый набор
допустимых
значений
• два механизма
определения
доменов:
• CHECK,
позволяющее
задать требуемые
ограничения для
столбца
• оператор CREATE
DOMAIN
Корпоративные
ограничения
целостности
• предложения
CHECK и
ключевое
слово UNIQUE
Целостность
сущностей
Ссылочная
целостность
• Задается с
помощью фразы
PRIMARY KEY
• Уникальность
значений и для
любых
альтернативных
ключей таблицы
обеспечивает
ключевое слово
UNIQUE
• внешний ключ
FOREIGN KEY

6.

ССЫЛОЧНАЯ ЦЕЛОСТНОСТЬ
Внешний ключ – это поле (или множество полей) одной таблицы,
являющееся ключом другой (или той же самой) таблицы.
Ссылочная целостность определяет: если в таблице существует внешний
ключ, то его значение должно либо соответствовать значению
первичного ключа некоторой записи в базовой таблице, либо задаваться
определителем NULL.
Первичный ключ
базовой таблицы
Внешний ключ

7.

ССЫЛОЧНАЯ ЦЕЛОСТНОСТЬ
FOREIGN KEY (имя_столбца_внешнего_ключа[,...n])
REFERENCES имя_род_таблицы[(имя_столбца_род_таблицы[,...n])
FOREIGN KEY - указывается столбец дочерней таблицы, определенный в качестве внешнего ключа
REFERENCES - имя родительской таблицы и столбцы ее первичного ключа
Данные в столбцах, определенных в качестве внешнего ключа, могут принимать только
такие же значения, какие находятся в связанных с ним столбцах первичного ключа
родительской таблицы.
Совпадение имен столбцов для связи дочерней и родительской таблиц необязательно.
Единственным требованием остается соответствие столбцов по типу и размеру
данных

8.

ON UPDATE и ON DELETE
При использовании FOREIGN KEY система отклонит выполнение любых операторов INSERT или
UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение
внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа
родительской таблицы.
Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих
попытку обновить или удалить значение потенциального ключа в родительской таблице, которому
соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной
целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY:
CASCADE - выполняется удаление (обновление) строки из родительской таблицы, сопровождающееся
автоматическим удалением (обновлением) всех ссылающихся на нее строк дочерней таблицы;
SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на
нее строк дочерней таблицы записывается значение NULL ;
SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся
на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;
NO ACTION - операция удаления (обновления) строки из родительской таблицы отменяется. Именно это
значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE
опущена.

9.

•ОПРЕДЕЛИТЕЛЬ MATCH ПОЗВОЛЯЕТ УТОЧНИТЬ СПОСОБ
ОБРАБОТКИ ЗНАЧЕНИЯ NULL ВО ВНЕШНЕМ КЛЮЧЕ.
•ФРАЗА
CONSTRAINT ПОЗВОЛЯЕТ ЗАДАТЬ ИМЯ
ОГРАНИЧЕНИЮ, ЧТО ПОЗВОЛИТ ВПОСЛЕДСТВИИ
ОТМЕНИТЬ ТО ИЛИ ИНОЕ ОГРАНИЧЕНИЕ С ПОМОЩЬЮ
ОПЕРАТОРА ALTER TABLE.

10.

ОГРАНИЧЕНИЕ ПРОВЕРОЧНОЕ CHECK
• ИСПОЛЬЗОВАНИЕ ЛОГИЧЕСКИХ ВЫРАЖЕНИЙ.
• ДОПУСКАЕТСЯ ПРИМЕНЕНИЕ НЕСКОЛЬКИХ ОГРАНИЧЕНИЙ CHECK К ОДНОМУ
И ТОМУ ЖЕ СТОЛБЦУ. В ЭТОМ СЛУЧАЕ ОНИ БУДУТ ПРИМЕНИМЫ В ТОЙ
ПОСЛЕДОВАТЕЛЬНОСТИ, В КОТОРОЙ ПРОИСХОДИЛО ИХ СОЗДАНИЕ

11.

ПРИМЕР 1. СОЗДАНИЕ РОДИТЕЛЬСКОЙ
ТАБЛИЦЫ ТОВАР С ОГРАНИЧЕНИЯМИ
CREATE TABLE Товар
(КодТовара INT IDENTITY(1,1) PRIMARY KEY,
Название VARCHAR(50) NOT NULL UNIQUE,
Цена
MONEY NOT NULL,
Тип
VARCHAR(50) NOT NULL,
Сорт
VARCHAR(50) NOT NULL CHECK (сорт in ('первый', 'второй')),
Город VARCHAR(50) NOT NULL,
Остаток INT CHECK(остаток>=0))

12.

ПРИМЕР 2. СОЗДАНИЕ РОДИТЕЛЬСКОЙ
ТАБЛИЦЫ КЛИЕНТ С ОГРАНИЧЕНИЯМИ
CREATE TABLE Клиент
(КодКлиента INT IDENTITY(1,1) PRIMARY KEY,
Фирма
VARCHAR(50)
NOT NULL,
Фамилия VARCHAR(50)
NOT NULL,
Город
VARCHAR(50)
NOT NULL,
Телефон CHAR(10)
NOT NULL
CHECK (Телефон LIKE '[1-9][0-9]-[0-9][0-9]-[0-9][0-9]'))

13.

ПРИМЕР 3. СОЗДАНИЕ ДОЧЕРНЕЙ
ТАБЛИЦЫ СДЕЛКА С ОГРАНИЧЕНИЯМИ
CREATE TABLE Сделка(
КодСделки INT IDENTITY(1,1) PRIMARY KEY,
КодТовара INT
NOT NULL,
КодКлиента INT
NOT NULL,
Количество INT
NOT NULL DEFAULT 0,
Дата DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT fk_Товар
FOREIGN KEY(КодТовара) REFERENCES Товар (КодТовара),
CONSTRAINT fk_Клиент
FOREIGN KEY(КодКлиента) REFERENCES Клиент(КодКлиента))

14.

ПРИМЕР 4. УДАЛЕНИЕ ОГРАНИЧЕНИЯ
ВНЕШНЕГО КЛЮЧА.
ALTER TABLE Сделка DROP CONSTRAINT fk_Товар

15.

ПРИМЕР 5. ДОБАВЛЕНИЕ ОГРАНИЧЕНИЯ ВНЕШНЕГО
КЛЮЧА
ALTER TABLE Сделка
ADD CONSTRAINT fk_Товар
FOREIGN KEY (КодТовара) REFERENCES товар (КодТовара)
ON UPDATE NO ACTION
ON DELETE NO ACTION

16.

ПРИМЕР 6. ДОБАВЛЕНИЯ ОГРАНИЧЕНИЯ ВНЕШНЕГО КЛЮЧА,
РЕАЛИЗУЮЩЕГО КАСКАДНЫЕ ОБНОВЛЕНИЯ И ИЗМЕНЕНИЯ
ALTER TABLE Сделка
ADD CONSTRAINT fk_Товар
FOREIGN KEY (КодТовара)
REFERENCES товар (КодТовара)
ON UPDATE CASCADE
ON DELETE CASCADE

17.

ПРИМЕР 7. ПОЛЕ КОДТОВАРА НЕОБХОДИМО
СДЕЛАТЬ ПЕРВИЧНЫМ КЛЮЧОМ.
Сначала нужно изменить объявление столбца КодТовара,
запретив внесение значений NULL:
ALTER TABLE Товар
ALTER COLUMN КодТовара INT NOT NULL
И только потом создать ограничение первичного ключа:
ALTER TABLE Товар
ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара)

18.

ПРИМЕР 8. УДАЛИТЬ СТОЛБЕЦ ЦЕЛОГО
ТИПА И ДОБАВИТЬ СТОЛБЕЦ-СЧЕТЧИК И
ОГРАНИЧЕНИЕ ПЕРВИЧНОГО КЛЮЧА
ALTER TABLE Товар DROP COLUMN КодТовара;
ALTER TABLE Товар ADD КодТовара INT IDENTITY(1,1);
ALTER TABLE Товар
ADD CONSTRAINT pk1
PRIMARY KEY(КодТовара) ;

19.

ЗАДАНИЯ
1. В ТАБЛИЦЕ ГОРОД ЗНАЧЕНИЯ ПОЛЯ ТАРИФ ОГРАНИЧИТЬ ДИАПАЗОНОМ ОТ 0.1 РУБ. ДО 7.8. РУБ.
А
ALTER TABLE Город
ADD CHECK(Тариф>=0.1 AND Тариф<=7.8)
В
ALTER TABLE Город
ALTER Тариф MONEY CHECK(Тариф >=0.1 AND Тариф<=7.8)
С
ALTER TABLE Город
ADD CHECK(Тариф <=0.1 AND >=7.8)
D
ALTER TABLE Город
ADD CONSTRAINT ch1 CHECK(Тариф Between 0.1 and 7.8)

20.

2. В ТАБЛИЦЕ БИЛЕТ ОГРАНИЧИТЬ ПОЛЕ
НОМЕР_МЕСТА ДИАПАЗОНОМ ОТ 0 ДО 250.
A
ALTER TABLE Билет
ADD CONSTRAINT ch1 CHECK(Номер_места Between 0 and 250)
B
ALTER TABLE Билет
ALTER Номер_места INT CHECK(Номер_места Between 0 and 250)
C
ALTER TABLE Билет
ADD CHECK(Номер_места Between 0 and 250)

21.

3. В ТАБЛИЦЕ АВТОР ОГРАНИЧИТЬ ПОЛЕ ПОЛ
ЗАДАННЫМ СПИСКОМ: М, Ж, МУЖ, ЖЕН
A
.
ALTER
TABLE Автор
ADD CONSTRAINT ch1 CHECK(пол IN ('м', 'ж', 'муж', 'жен'))
B
ALTER TABLE Автор
ADD CHECK(пол IN ('м', 'ж', 'муж', 'жен'))
C
ALTER TABLE Автор
ALTER пол VARCHAR(3) CHECK(пол IN ('м','ж','муж','жен'))

22.

4. В DB-FIDDLE.COM СОЗДАТЬ ТАБЛИЦЫ С
ОГРАНИЧЕНИЯМИ ПЕРВИЧНОГО И ВНЕШНЕГО КЛЮЧА.
ТАБЛИЦЫ ЗАПОЛНИТЬ ДАННЫМИ.