sql

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

от

Из статьи вы узнаете про ограничение целостности SQL. А также, на примере PostgreSQL, я покажу, как эти ограничения создаются.

Оглавление скрыть
Теория
Создание таблицы с ограничениями целостности
Создание ограничения для существующей таблицы
NOT NULL
UNIQUE
CHECK

Теория

В прошлой статье: «Базовые команды SQL» я показывал как создавать таблицы в СУБД PostgreSQL. Первичный ключ уже накладывает некоторые ограничения целостности. Например, все значения в поле, которое является первичным ключом должны быть уникальными.

Ограничение целостности SQL, это определённое требование для полей в таблице SQL. За соблюдение этих требований отвечает сама СУБД. Например:

  • Тип данных накладывает некоторое ограничение. Нельзя в поле с типом «integer» вставить текстовую строку. Но для некоторых типов можно ещё ужесточить правила:
    • для типа «char» и «varchar» можно указать максимальную длину строки. Например, в поле «vaechar(4)» нельзя будет вставить строку из пяти символов.
  • Первичный ключ сразу накладывает несколько ограничений, хотя их можно наложить и поотдельности:
    • записи должны быть уникальны (unique);
    • записи не могут быть пустыми (not null).
  • Есть определённые проверки (check). С их помощью можно наложить дополнительные ограничения. Например, для цифрового значения можно указать что число должно быть больше 50, но меньше 200. Или поле, которое хранит пол человека, может принимать только два значения: «М» или «Ж».

В документации об ограничениях можете почитать тут.

В этой статье будем говорить о «check» проверках, так как о первичном ключе и типах данных я уже рассказывал. Также покажу как создаются ограничения unique и not null.

Создание таблицы с ограничениями целостности

Создадим новую базу данных и подключимся к ней:

postgres=# CREATE DATABASE check_constraint;
CREATE DATABASE

postgres=# \c check_constraint
You are now connected to database "check_constraint" as user "postgres".

Создадим таблицу staff (сотрудники):

НазваниеОписаниеТип данныхОграничения
( NOT NULL, UNIQUE )		Ограничения
(CHECK)
numberномерserialNOT NULL, UNIQUE
fullnameфиоvarchar (50)NOT NULL
sexполchar (1)NOT NULLМожет быть только «М» или «Ж»
ageвозрастintegerNOT NULLОт 18 до 100 (я думаю больше 100 лет сотруднику не может быть)
experienceстажintegerNOT NULLСтаж должен быть меньше возраста

Для создания подойдёт следующий запрос:

# CREATE TABLE staff
(number serial NOT NULL UNIQUE,
fullname varchar(50) NOT NULL,
sex char(1) NOT NULL, CHECK ( sex IN ('М', 'Ж')),
age integer NOT NULL, CHECK ( age > 18 AND age < 100),
experience integer NOT NULL, CHECK ( experience < age)
);
CREATE TABLE

И посмотрим на то, что у нас получилось:

# \d staff
                                       Table "public.staff"
   Column   |         Type          | Collation | Nullable |                Default
------------+-----------------------+-----------+----------+---------------------------------------
 number     | integer               |           | not null | nextval('staff_number_seq'::regclass)
 fullname   | character varying(50) |           | not null |
 sex        | character(1)          |           | not null |
 age        | integer               |           | not null |
 experience | integer               |           | not null |
Indexes:
    "staff_number_key" UNIQUE CONSTRAINT, btree (number)
Check constraints:
    "staff_age_check" CHECK (age > 18 AND age < 100)
    "staff_check" CHECK (experience < age)
    "staff_sex_check" CHECK (sex = ANY (ARRAY['М'::bpchar, 'Ж'::bpchar]))

Попробуем создать 15 летнего сотрудника:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Иванов Иван Алексеевич', 'М', 15, 10);
ERROR:  new row for relation "staff" violates check constraint "staff_age_check"
DETAIL:  Failing row contains (1, Иванов Иван Алексеевич, М, 15, 10).

Как видно из ошибки, сработало ограничение staff_age_check.

А 18 летний создастся нормально:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Иванов Иван Алексеевич', 'М', 20, 10);
INSERT 0 1

А что будет, если ошибёмся с полом:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Донченко Иван Андреевич', 'К', 25, 8);
ERROR:  new row for relation "staff" violates check constraint "staff_sex_check"
DETAIL:  Failing row contains (3, Донченко Иван Андреевич, К, 25, 8).

Здесь уже сработало ограничение staff_sex_check.

Если мы что-то забудем указать, то сработает ограничение NOT NULL:

# INSERT INTO staff (fullname, sex, age)
VALUES ('Донченко Иван Андреевич', 'Ж', 25);
ERROR:  null value in column "experience" of relation "staff" violates not-null constraint
DETAIL:  Failing row contains (4, Донченко Иван Андреевич, Ж, 25, null).

И наконец введем второго пользователя в базу:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Донченко Иван Андреевич', 'М', 25, 8);
INSERT 0 1

А теперь посмотрим на табличку:

check_constraint=# SELECT * FROM staff;
 number |        fullname         | sex | age | experience
--------+-------------------------+-----+-----+------------
      2 | Иванов Иван Алексеевич  | М   |  20 |         10
      5 | Донченко Иван Андреевич | М   |  25 |          8
(2 rows)

Как вы могли заметить последовательность постоянно растёт, даже если мы ошибаемся при вставке. Поэтому у нас в поле number вначале 2, а затем 5.

Создание ограничения для существующей таблицы

NOT NULL

Добавить или удалить ограничение целостности NOT NULL можно с помощью ALTER TABLE таким образом:

# ALTER TABLE staff ALTER COLUMN number DROP NOT NULL;
# ALTER TABLE staff ALTER COLUMN number SET NOT NULL;

UNIQUE

Ограничения целостности UNIQUE удаляются с помощью DROP CONSTRAINT, а добавляется с помощью ADD CONSTRAINT: 

# ALTER TABLE staff DROP CONSTRAINT staff_number_key;
# ALTER TABLE staff ADD CONSTRAINT staff_number_key UNIQUE (number);

Кстати, при удалении ограничения целостности UNIQUE удаляется и индекс.

CHECK

Ограничения целостности CHECK создаются и удаляются подобным способом:

# ALTER TABLE staff DROP CONSTRAINT staff_age_check;
# ALTER TABLE staff ADD CONSTRAINT staff_age_check CHECK (age > 18 AND age < 100);

При этом названия ограничения (staff_number_key, staff_age_check и другие) вы придумываете сами.

Оставьте комментарий