Создание первичного ключа

1. Для предотвращения создания записей-дубликатов добавим к описанию таблицы правило уникальности. Добавим первичный ключ.

alter table exdoc

add constraint pk_exdoc

primary key(doc_nd, doc_dd);

Таблица изменена.

Рассмотрим команду.

Команда требует указать имя таблицы, это exdoc.

Затем, указываем, что будем добавлять в описание таблицы – ключевое слово add. Вспомним, что до этого использовали ALTER TABLE для изменения описания - modify.

Сообщаем, что добавляем правило-ограничение, ключевое слово constraint. После него пишем имя правила pk_exdoc.

Определяем тип правила – первичный ключ primary key. В круглых скобках перечисляем колонки. В этой команде создается составной первичный ключ.

2. Запомним команду.

save alt_exdoc4

3. Добавим запись из тестового набора.

@ins_exdoc

4. Попробуем воспроизвести ситуацию с дублированием.

insert into exdoc select * from exdoc;

Получили ошибку. Указано имя правила-ограничения.

Пересоздание таблицы с позициями счетов

Добавим правила для обязательного заполнения колонок в таблицу с позициями expos. Для добавления правил будем использовать не ALTER TABLE, а команду CREATE TBALE. Но прежде удалим таблицу.

1. Смотрим структуру таблицы

desc expos

Все колонки могут принимать значение NULL.

2. Удалим таблицу.

drop table expos;

3. Загрузим в буфер ввода текст команды CRETE TABLE для таблицы expos.

get cre_expos

4. Перейдем в текстовый редактор и изменим команду следующим образом.

edit

create table expos

( doc_nd number ( 6) not null

, doc_dd date not null

, pos_no number ( 5) not null

, good_n varchar2( 60) not null

, ed varchar2( 6) not null

, quant number ( 9, 2) not null

, price number ( 9, 2) not null

, cost number ( 12, 2)

, tax number ( 12, 2) not null

, sum_tax number ( 12, 2)

, sum_pos number ( 12, 2)

)

Для колонок pos_no, good_n, ed, price, quant, tax после размерности введём ключевое слово “not null”.

5. Выполним "/".

6. Сохраним команду в файле с другим именем.

save cre_expos2

7. Посмотрим на структуру.

desc expos

Видим, что некоторые колонки теперь не могут принимать значение NULL.

Добавим проверку значений

1. Добавим правило проверки значений в колонке pos_no. Номер позиции должен начинаться с единицы. Скажем проще, должен быть больше нуля.

alter table expos

add constraint ck_expos_pos_no

check( pos_no > 0);

Правило проверки значений имеет имя ck_expos_pos_no. Тип правила – check, т.е. проверка значения. Диапазон задан в круглых скобках - это простое логическое выражение.

2. Сохраним команду.

save alt_expos1

3. Добавляем правило для проверки значений в колонке price и quant.

alter table expos

add constraint ck_expos_quant

check( quant > 0);

4. save alt_expos2

5.

alter table expos

add constraint ck_expos_price

check( price > 0);

6. save alt_expos3

7. Добавляем правило для ставки налога.

alter table expos_tax

add constraint ck_expos_tax

check( tax =10 OR tax = 18);

Обращаю внимание на составное условие: две простых проверки объединены логическим оператором OR. Должна выполниться либо левая, либо правая часть условия.

8. save alt_expos4

9. Добавим правило ссылочной целостности. Пара значений из колонок doc_nd и doc_dd должна присутствовать в таблице exdoc.

Вводим команду:

alter table expos

add constraint fk_expos_exdoc

foreign key(doc_nd, doc_dd)

references exdoc( doc_nd, doc_dd);

В этой команде добавляем правило ссылочной целостности – foreign key. В круглых скобках перечисляем колонки, значения которых необходимо проверять. После ключевого слова references указываем имя таблицы и список колонок, в которой должны присутствовать проверяемые значения.

10. Запомним команду.

save alt_expos5

11. Добавим первичный ключ в таблицу expos.

alter table expos

add constraint pk_expos

primary key(doc_nd, doc_dd, pos_no);

Уникальность задаётся комбинацией из трех значений.

Обращаю внимание:

• комбинация из пары значений, в колонках doc_nd и doc_dd, обязательно должна быть в таблице exdoc;

• значение в колонке pos_no должно быть целым числом больше 0.

Вспоминаем правила ссылочной целостности, проверку значений и смотрим на тип колонки pos_no.

12. Сохраним команду.

save alt_expos6

Пора проверить работу ограничений. Почистим таблицы и добавим тестовые записи.

13. delete from exdoc;

14. delete from expos;

15. @ins_exdoc

16. @ins_expos1

Добавление записей прошло успешно, т.е. она удовлетворяет всем правилам.

17. Умышленно вносим некорректные данные. Изменяем значение колонки doc_nd на 12. Счёт с номером 12 в таблице exdoc отсутствует.

edit ins_expos1

Внесите правки самостоятельно.

18. Выполните файл ins_expos1. Получили ошибку? Какое правило нарушено?

19. Ещё раз откройте файл ins_expos1 в режиме редактирования, верните правильный номер счёта 137. Измените процент налоговой ставки на 20.

20. Выполните файл ins_expos1. Понятен текст ошибки? Что нарушено?

21. Восстановите правильные значения в файле ins_expos1.

Лекция 9: Практическое занятие.