Материал: SQL в вопросах и задачах
96
4.5.7.
П роверка
з начений
пол ей
О гра нич ение
CHECK
п оз в оляет
оп р ед елять
услов ие
,
котор ому
д олжно
уд ов летв орять
в в од имое
в
п оле
та блицы
з на ч ение
,
п режд е
ч ем
оно
буд ет
п ринято
.
Л юба я
п оп ытка
обнов ить
или
з а менить
з на ч ение
п оля
та кими
,
д ля
которых
п ред ика т
,
з а д а в а емый
огранич ением
CHECK
,
имеет
з на ч ение
ло ж ь
,
буд ет
отв ерга ться
.
Ра ссмотрим
та блицу
STUDENT
.
Зна ч ение
столбца
STIPEND
в
э той
та блице
STUDENT
в ыража ется
д есятич ным
ч ислом
.
Н а ложим
на
з на ч ения
э того
столбца
след ующ ее
огранич ение
–
в елич ина
раз мера
стип енд ии
д олжна
быть
меньше
200.
Соотв етств ующ ий
з а п рос
имеет
след ующ ий
в ид
.
CREATE
TABLE
STUDENT
(
STUDENT
_
ID
INTEGER
PRIMARY
KEY
,
SURNAME
CHAR
(
25
)
NOT
NULL
,
NAME
CHAR
(
10
)
NOT
NULL
,
STIPEND
INTEGER
CHECK
(
STIPEND
<
200
),
KURS
INTEGER
,
CITY
CHAR
(
15
),
BIRTHDAY
DATE
,
UNIV
_
ID
INTEGER
);
4.5.8.
П роверка
ограничиваю щ их
усл овий
с
испол ь з ованием
сост авны х
пол ей
О гра нич ение
CHECK
можно
исп ользов а ть
в
ка ч еств е
та блич ного
огранич ения
,
то
есть
п ри
необход имости
в ключ ить
более
од ного
п оля
строки
в
огранич ив а ющ ее
услов ие
.
Пред п оложим
,
ч то
огранич ение
на
раз мер
стип енд ии
(
меньше
200)
д олжно
ра сп ространяться
только
на
студ ентов
,
жив ущ их
в
Воронеже
.
Э то
можно
ука з а ть
в
з а п росе
со
след ующ им
та блич ным
огранич ением
CHECK
:
97
CREATE
TABLE
STUDENT
(
STUDENT
_
ID
INTEGER
PRIMARY
KEY
,
SURNAME
CHAR
(
25
)
NOT
NULL
,
NAME
CHAR
(
10
)
NOT
NULL
,
STIPEND
INTEGER
,
KURS
INTEGER
,
CITY
CHAR
(
15
),
BIRTHDAY
DATE
,
UNIV
_
ID
INTEGER
UNIQUE
,
CHECK
(
STIPEND
<
200
AND
CITY
=
‘
Во р о н еж
’
));
или
в
несколько
д ругой
з а п иси
CREATE
TABLE
STUDENT
(
STUDENT
_
ID
INTEGER
PRIMARY
KEY
,
SURNAME
CHAR
(
25
)
NOT
NULL
,
NAME
CHAR
(
10
)
NOT
NULL
,
STIPEND
INTEGER
,
KURS
INTEGER
,
CITY
CHAR
(
15
),
BIRTHDAY
DATE
,
UNIV
_
ID
INTEGER
UNIQUE
,
CONSTRAINT
STUD
_
CHECK
CHECK
(
STIPEND
<
200
AND
CITY
=
‘
Во р о н еж
’
));
4.5.9.
Уст ановка
з начений
по
ум ол чанию
В
SQL
имеется
в оз можность
п ри
в ста в ке
в
та блицу
строки
,
не
ука з ыв а я
з на ч ений
некоторого
п оля
,
оп ред елять
з на ч ение
э того
п оля
п о
умолч а нию
.
Н а иболее
ч а сто
исп ользуемым
з на ч ением
п о
умолч а нию
яв ляется
NULL
.
Э то
з на ч ение
п ринима ется
п о
умолч а нию
д ля
любого
столбца
,
д ля
которого
не
было
уста нов лено
огранич ение
NOT
NULL
.
Зна ч ение
п оля
п о
умолч а нию
ука з ыв а ется
в
кома нд е
CREATE
TABLE
тем
же
сп особом
,
ч то
и
огра нич ение
столбца
,
с
п омощ ью
ключ ев ого
слов а
DEFAULT
<
зна чени е
по
ум о лча ни ю
>.
Строго
гов оря
,
оп ция
DEFAULT
не
имеет
огранич ительного
св ойств а
,
та к
ка к
она
не
огра нич ив а ет
з на ч ения
,
в в од имые
в
п оле
,
а
п р осто
98
конкретиз ирует
з на ч ение
п оля
в
случ а е
,
если
оно
не
бы ло
за д а но
.
Пред п оложим
,
ч то
основ на я
ма сса
студ ентов
,
информа ция
о
которых
на ход ится
в
та блице
STUDENT
,
п рожив а ет
в
Воронеже
.
Ч тобы
п ри
з а д а нии
а трибутов
не
в в од ить
д ля
большинств а
студ ентов
на з в а ние
город а
‘
Во р о н еж
’
,
можно
уста нов ить
его
ка к
з на ч ение
п оля
CITY
п о
умолч а нию
,
оп ред елив
та блицу
STUDENT
след ующ им
образ ом
:
CREATE
TABLE
STUDENT
(
STUDENT
_
ID
INTEGER
PRIMARY
KEY
,
SURNAME
CHAR
(
25
)
NOT
NULL
,
NAME
CHAR
(
10
)
NOT
NULL
,
STIPEND
INTEGER
CHECK
(
STIPEND
<
200
),
KURS
INTEGER
,
CITY
CHAR
(
15
)
DEFAULT
‘
Во р о н еж
’
,
BIRTHDAY
DATE
,
UNIV
_
ID
INTEGER
);
Д руга я
цель
п рактич еского
п рименения
з а д а ния
з на ч ения
п о
умолч а нию
–
э то
исп ользов а ние
его
ка к
а льтерна тив ы
д ля
NULL
.
К а к
уже
отмеч а лось
в ыше
,
п рисутств ие
NULL
в
ка ч еств е
в оз можных
з на ч ений
п оля
сущ еств енно
усложняет
интерп рета цию
оп ераций
срав нения
,
в
которых
уч а ств уют
з на ч ения
та ких
п олей
,
п оскольку
NULL
п ред ста в ляет
собой
п риз на к
того
,
ч то
фа ктич еское
з на ч ение
п оля
неи зв ест но
или
нео пр ед еленно
.
След ов а тельно
,
строго
гов ор я
,
срав нение
с
ним
любого
конкретного
з на ч ения
в
рамка х
д в уз на ч ной
булев ой
логики
яв ляется
не
корр ектным
,
з а
исключ ением
сп ециа льной
оп ерации
сра в нения
IS
NULL
,
котор а я
оп ред еляет
,
яв ляется
ли
сод ержимое
п оля
ка ким
-
либо
з на ч ением
или
оно
отсутств ует
.
Д ейств ительно
,
ка ким
образ ом
в
рамка х
д в уз на ч ной
логики
отв етить
на
в оп рос
истинно
или
ложно
услов ие
:
CITY
=
‘
Во р о н еж
’
,
если
текущ ее
з на ч ение
п оля
CITY
не
из в естно
(
сод ержит
NULL
).
Во
многих
случ а ях
исп ользов а ние
в место
NULL
з на ч ения
,
п од ста в ляемого
в
п оле
п о
умолч а нию
,
может
сущ еств енно
уп ростить
исп ользов а ние
з на ч ений
п оля
в
п ред ика та х
.
Н а п ример
,
можно
уста нов ить
д ля
столбца
оп цию
NOT NULL
,
а
д ля
неоп ред еленных
з на ч ений
ч ислов ого
тип а
уста нов ить
з на ч ение
п о
99
умолч а нию
“рав но
нулю”
,
или
д ля
п олей
тип а
CHAR
–
п робел
,
исп ользов а ние
которых
в
оп ера циях
сра в нения
не
в ыз ыв а ет
ника ких
п роблем
.
При
исп ользов а нии
з на ч ений
п о
умолч а нию
в
п ринцип е
д оп устимо
п рименять
огранич ения
UNIQUE
или
PRIMARY
KEY
в
э том
п оле
.
При
этом
,
од на ко
,
след ует
иметь
в
в ид у
отсутств ие
в
та ком
огранич ении
п ра ктич еского
смысла
,
п оскольку
только
од на
стр ока
в
та блице
сможет
п ринять
з на ч ение
,
сов п а д а ющ ее
с
э тим
з на ч ением
п о
умолч а нию
.
Д ля
та кого
п рименения
з а д а ния
п о
умолч а нию
необход имо
(
д о
в ста в ки
д ругой
строки
с
уста нов кой
п о
умолч а нию
)
мод ифициров а ть
п ред ыд ущ ую
строку
,
сод ержа щ ую
та кое
з на ч ение
.
УП Р АЖ НЕ НИ Я
1.
Соз д а йте
та блицу
EXAM
_
MARKS
та к
,
ч тобы
не
д оп уска лся
в в од
в
та блицу
д в ух
з а п исей
об
оценка х
од ного
студ ента
п о
конкретным
э кз а мену
и
п ред мету
обуч ения
,
а
та кже
,
ч тобы
не
д оп уска лось
п ров ед ение
д в ух
э кз а менов
п о
любым
п ред мета м
в
од ин
д ень
.
2.
Соз д а йте
та блицу
п ред метов
обуч ения
SUBJECT
та к
,
ч тобы
колич еств о
отв од имых
на
п ред мет
ч а сов
п о
умолч а нию
было
рав но
36,
не
д оп уска лись
з а п иси
с
отсутств ующ им
колич еств ом
ч а сов
,
п оле
SUBJ
_
ID
яв лялось
п ер в ич ным
ключ ом
та блицы
,
и
з на ч ения
семестров
(
п оле
SEMESTR
)
лежа ли
в
д иа п а з оне
от
1-
го
д о
12-
ти
.
3.
Соз д а йте
та блицу
EXAM
_
MARKS
та ким
образ ом
,
ч тобы
з на ч ения
п оля
EXAM
_
ID
были
больше
з на ч ений
п оля
SUBJ
_
ID
,
а
з на ч ения
п оля
SUBJ
_
ID
были
больше
з на ч ений
п оля
STUDENT
_
ID
;
п усть
та кже
буд ут
з а п рещ ены
з на ч ения
NULL
в
любом
из
э тих
трех
п олей
.
100
4.6.
П оддерж ка
ц ел остности
данны х
В
та блица х
рассма трив а емой
ба з ы
д а нных
з на ч ения
некотор ых
п олей
св яз а ны
д руг
с
д ругом
.
Та к
п оле
STUDENT
_
ID
в
та блице
STUDENT
и
п оле
STUDENT
_
ID
в
та блице
EXAM_MARKS
св яз а ны
тем
,
ч то
оп исыв а ют
од ни
и
те
же
объекты
,
то
есть
сод ержа т
ид ентифика торы
студ ентов
,
инфор ма ция
о
которых
хранится
в
ба з е
.
Б олее
того
,
з на ч ения
ид ентифика тор ов
студ ентов
,
которые
д оп устимы
в
та блице
EXAM_MARKS
,
д олжны
в ыбираться
только
из
сп иска
з на ч ений
STUDENT
_
ID
,
фа ктич ески
п р исутств ующ их
в
та блице
STUDENT
,
то
есть
п рина д лежа щ их
реа льно
оп иса нным
в
ба з е
студ ента м
.
А на логич но
,
з на ч ения
п оля
UNIV
_
ID
та бл ицы
STUDENT
д олжны
соотв етств ов а ть
ид ентифика торам
унив ерситетов
UNIV
_
ID
,
фа ктич ески
п рисутств ующ им
в
та блице
UNIVERSITY
,
а
з на ч ения
п оля
SUBJ
_
ID
та блицы
EXAM
_
MARKS
д олжны
соотв етств ов а ть
ид ентифика тора м
п ред метов
обуч ения
,
фа ктич ески
п рисутств ующ им
в
та бл ице
SYBJECT
.
О гра нич ения
,
на кла д ыв а емые
ука з а нным
тип ом
св яз и
,
на з ыв а ются
о гр а ни чени ям и
ссы ло чно й
цело ст но ст и
.
О ни
соста в ляют
в а жную
ч а сть
оп иса ния
ха р а ктеристик
п ред метной
обла сти
,
обесп еч ения
корректности
д а нных
,
хранящ ихся
в
та блица х
.
К ома нд ы
оп иса ния
та блиц
DML
имеют
сред ств а
,
п оз в оляющ ие
оп исыв а ть
огра нич ения
ссылоч ной
целостности
и
обесп еч ив а ть
п од д ержа ние
та кой
целостности
п р и
ма нип уляциях
з на ч ениями
п олей
ба з ы
д а нных
.
4.6.1.
В неш ние
и
родит ел ь ские
кл ю чи
К огд а
ка жд ое
з на ч ение
,
п р исутств ующ ее
в
од ном
п оле
та блицы
,
п ред ста в лено
в
д ругом
п оле
д ругой
или
э той
же
та блицы
,
гов орят
,
ч то
п ер в ое
п оле
ссыла ется
на
в торое
.
Э то
ука з ыв а ет
на
п рямую
св яз ь
межд у
з на ч ениями
д в ух
п олей
.
Поле
,
которое
ссыла ется
на
д ругое
п оле
,
на з ыв а ется
в неш ним
к лю ч ом
,
а
п оле
,
на
которое
ссыла ется
д ругое
п оле
,
на з ыв а ется
р одит ель ск им
к лю ч ом
.
Та к
ч то
п оле
UNIV
_
ID
та блицы
STUDENT
–
это
в нешний
ключ
(
оно
ссыла ется
на
п оле
д ругой
та блицы
),
а
п оле
UNIV
_
ID
та блицы
UNIVERSITY
,
на
ко т о р о е
ссыла ется
э тот
в нешний
ключ
–
э то
род ительский
ключ
.