| Тип данных | Назначение |
| smallmoney, money | Денежный тип, разновидность числового типа с фиксированной точкой, предназначен для хранения значения денежных величин |
| xml | XML-тип предназначен для хранения данных, представленных в формате XML |
| hierarhied | Иерархический тип данных, используется для отражения иерархических (древовидных) связей между строками таблицы |
| geography, geometry | Пространственные данные, предназначенные для хранения географических координат, геометрических координат и геометрических (2D) объектов |
| sqlvariant | Универсальный тип, способный хранить числовые, символьные данные, а также данные для хранения даты и времени. Непосредственно перед использованием, данные могут быть преобразованы к необходимому типу |
| text, ntext, image | Устаревшие типы данных, поддерживаются для совместимости с предыдущими версиями сервера, заменены на varchar(max) и varbinary(max) |
5.2.1.8. Преобразование типов. Для преобразования типов предусмотрены две встроенные функции CAST и CONVERT. По своим возможностям функции практически не отличаются, но функция CAST соответствует стандарту SQL-92, поэтому ее применение предпочтительнее.
На рис. 5.17 и 5.18 приведены примеры использования функций CAST и CONVERT. Обратите внимание, что в предпоследнем SELECT-запросе в функции CONVERT есть третий параметр, который задается числовым значением. Этот параметр применяется при преобразованиях, использующих типы данных для хранения даты и времени, и задает стиль представления этих данных.
Рис. 5.17. Пример использования встроенных функций преобразования типов
Рис. 5.18. Результаты выполнения SELECT-запросов на рис. 5.17
Для более подробного знакомства с функциями преобразования типов данных рекомендуется пособие [5].
Ограничения целостности
Рассмотрим на рис. 5.19 упрощенный пример создания таблицы с именем S_ AUDITORIUM. Обратим внимание, что столбец с именем AUDUTORIUM_ CAPACITY имеет тип INT (целочисленное значение).
Попытка ввода в этот столбец значения, которое не может быть автоматически преобразовано к целому числу (рис. 5.20), приводит к ошибке (рис. 5.21), сопровождаемой сообщением сервера.
Рис. 5.19. Таблица, имеющая столбец с типом данных INT
Рис. 5.20. Добавление строки в таблицу S_AUDITORIUM
Рис. 5.21. Сообщение об ошибке при попытке преобразования строки к типу INT
Аналогичный результат был бы получен при попытке с помощью оператора UPDATE заменить существующее целочисленное значение в столбце AUDITORIUM_ CAPACITY на значение другого типа.
Ограничения, накладываемые на столбцы таблицы, предотвращающие появление в БД данных, не соответствующих предварительно заданным свойствам таблицы, называются ограничениями целостности.
В простейшем случае ограничения целостности не допускают появление в БД данных, не соответствующих типу, как в приведенном выше (рис. 5.19–5.20) примере.
Помимо проверки типа данных при создании таблицы БД имеется возможность описать более сложные ограничения. Все типы таких ограничений целостности перечислены в табл. 5.7.
Таблица 5.7
Ограничения целостности
| Условное обозначение ограничения целостности | Действие ограничения целостности |
| data type тип данных | Предотвращает появление в столбце значений, не соответствующих типу данных |
| not null запрет значений null | Предотвращает появление в столбце значений null |
| default значение по умолчанию | Устанавливает значение в столбце по умолчанию при выполнении операции INSERT |
| primary key первичный ключ | Предотвращает появление в столбце (группе столбцов) повторяющихся значений (комбинации значений) и пустого значения (комбинации пустых значений) |
| foreign key внешний ключ | Устанавливает связь между таблицей со столбцом, имеющим свойство foreign key (FK) и таблицей, имеющей столбец со свойством primary key (PK); предотвращает не согласованные операции между PK и FK |
| unique уникальное значение | Аналогично primary key, но допускает пустые значения и не может быть использован для связи с foreign key |
| check проверка значений | Предотвращается появление в столбце значения, не удовлетворяющего логическому условию |
Для ограничений целостности PRIMARY KEY, FOREIGN KEY, UNIQUE и CHECK может быть задано имя, которое при возникновении ошибки, связанной с этим ограничением, будет указано в сообщении сервера. В том случае, если это имя не задано, при создании таблицы сервер назначает ограничениям этих типов собственные имена.
5.2.2.1. Первичный ключ. Одним из требований, предъявляемых к нормализованной таблице, является наличие одного или нескольких столбцов, значения в которых однозначно определяет каждую строку таблицы. Другими словами, в любой нормализованной таблице должен быть столбец (несколько столбцов), в котором значения (комбинация значений) являются разными в разных строках. Столбец или группа столбцов, обладающих таким свойством, называется ключом. Причем во втором случае, как правило, говорят о составном ключе.
На рис. 5.22 представлен пример SQL-сценария, демонстрирующий таблицу без первичного ключа. Обратите внимание, что в таблицу с помощью оператора INSERT добавляются две одинаковые строки. Результат SELECT-запроса (рис. 5.23) подтверждает наличие в одной таблице FACULTY двух одинаковых строк.
Сценарий на рис. 5.24 отличается от сценария на рис. 5.22 наличием свойства PRIMARY KEY, указанным для столбца FACULTY.
Рис. 5.22. Создание таблицы, не имеющей первичного ключа
Рис. 5.23. Результат выполнения SELECT-запроса на рис. 5.22
При выполнении второго оператора INSERT, с помощью которого осуществляется попытка ввода строки с таким же значением в столбце FACULTY, что и в первом операторе, возникает ошибка, сообщение о которой формируется сервером (рис. 5.25).
Рис. 5.24. Создание таблицы с первичным ключом
В сообщении об ошибке содержится: код ошибки (2627); уровень серьезности ошибки (14); метка (1); номер строки сценария (4), в которой находится оператор, вызвавший ошибку; сообщение об ошибке.
Следует обратить внимание, что в сообщении после слова CONSTRAINT следует непрерывная строка символов, обозначающих имя нарушенного ограничения, присвоенное сервером при создании таблицы FACULTY.
Рис. 5.25. Сообщение об ошибке, сформированное сервером при выполнении сценария на рис. 5.23
Результат выполнения SELECT-запроса (рис. 5.26) подтверждает, что после выполнения двух операторов INSERT была добавлена только одна строка.
Рис. 5.26. Результат выполнения SELECT-запроса на рис. 5.24
Для того, чтобы задать собственное имя ограничению, следует использовать ключевое слово CONTSTRAINT в синтаксической конструкции PRIMARY KEY (рис. 5.27, 5.28).
Рис. 5.27. Применение ключевого слова CONSTRAINT
Рис. 5.28. Сообщение об ошибке, содержащее имя ограничения, указанное при создании таблицы на рис. 5.27
Синтаксис оператора CREATE TABLE допускает другую форму записи ограничения (рис. 5.29).
Рис. 5.29. Другой способ записи ограничения PRIMARY KEY
На рис. 5.30 представлен пример создания таблицы с составным первичным ключом. В этом случае вторая форма записи ограничения является единственно возможной.
Рис. 5.30. Создание таблицы, имеющей составной первичный ключ
Обратите внимание, что имена двух столбцов в создаваемой таблице (рис. 5.30) обрамлены квадратными скобками. Такой синтаксис применяется в том случае, если имя столбца совпадает с каким-нибудь ключевым словом или содержит специальные символы (в том числе пробелы).
Все примеры, приведенные выше, демонстрировали действие ограничения целостности PRIMARY KEY при выполнении операторов INSERT, но ограничение действует и для операторов UPDATE.
На рис. 5.31 приведен результат выполнения SELECT-запроса к таблице FACULTY. В таблице содержится две строки, имеющие разные значения в столбце FACULTY. Попытка выполнить оператор UPDATE (рис. 5.32), нарушающий заданное ограничение PRIMARY KEY (рис. 5.29), приводит к ошибке аналогичной той, что возникала при INSERT (рис. 5.28)
Рис. 5.31. Содержимое таблицы FACULTY до выполнения оператора UPDATE
Рис. 5.32. Оператор UPDATE, нарушающий ограничение
PRIMARY KEY таблицы FACULTY
Кроме того, что ограничение PRIMARY KEY запрещает повторение значений в столбце (или комбинации значений при составном ключе), оно запрещает ввод пустых (NULL) значений в столбцы, входящие в первичный ключ. На рис. 5.33 приведены операторы, устанавливающие пустое значение (NULL) в столбце FACULTY. Результат выполнения сценария представлен на рис. 5.34.
Рис. 5.33. Операторы UPDATE и INSERT, устанавливающие пустое значение (NULL) в столбце FACULTY
Следует обратить внимание, что в сообщениях об ошибках на рис. 5.34 отсутствует имя ограничения PK_ FACULTY_ FACULTY. Дело в том, что свойство столбца PRIMARY KEY автоматически включает свойство (и соответствующее ему ограничение) NOT NULL, которое будет рассматриваться ниже. Именно это ограничение приводит к ошибкам при выполнении операторов INSERT и UPDATE из сценария на рис. 5.33.
Рис. 5.34. Результат выполнения сценария, представленного на рис. 5.33
5.2.2.2. Значение NULL. Столбцы любых типов данных могут содержать значение, обозначаемое символом NULL и интерпретируемое как неизвестная величина или пустое значение.
В строке таблицы значение NULL может появиться в результате выполнения операторов INSERT или UPDATE. На рис. 5.35 представлены примеры операторов INSERT, формирующих значения NULL в столбце FACULTY_ NAME.
Рис. 5.35. Операторы INSERT, формирующие значение NULL
На рис. 5.36 отображен результат выполнения SELECT-запроса, выводящего в результирующий набор все строки таблицы FACULTY. Во второй и третьей строках в столбце с именем FACULTY_ NAME содержатся значения NULL.
Рис. 5.36. Результат выполнения SELECT-запроса на рис. 5.35
Обратите внимание: оператором INSERT значение NULL формируется в столбцах в тех случаях, если значение в столбец не вводится (второй оператор INSERT) или NULL вводится явно (третий оператор INSERT).
Значение NULL может быть явно записано в столбцы существующих строк с помощью оператора UPDATE (рис. 5.37, 5.38).
Рис. 5.37. Явная запись значения NULL с помощью оператора UPDATE
Рис. 5.38. Результат выполнения SELECT-запроса на рис. 5.37
В тех случаях, когда требуется запретить в столбце таблицы значение NULL, применяется ограничение NOT NULL. На рис. 5.39 представлен пример использования этого ограничения целостности для столбца FACULTY_ NAME.
Сразу после создания таблицы выполняются три оператора INSERT и один оператор UPDATE. С помощью второго и третьего операторов INSERT выполняется попытка ввода значения NULL в столбец FACULTY_ NAME. Следующий далее оператор UPDATE делает попытку заменить содержимое этого же столбца на значение NULL. В результате выполнения этих трех операторов формируется три сообщения об ошибках (рис. 5.40). Обратите внимание, что сообщения об ошибке аналогичны сообщениям, формируемым при попытке установить значение NULL в столбец, для которого установлено ограничение PRIMARY KEY.
Рис. 5.39. Пример использования ограничения NOT NULL
Рис. 5.40. Сообщения об ошибках, возникших при выполнении
сценария на рис. 5.39
В тоже время первый оператор INSERT (рис. 5.39) выполнен успешно, т. к. он не нарушает ограничения целостности NOT NULL, что подтверждается результатом выполнения оператора SELECT.
Рис. 5.41. Результат выполнения SELECT-запроса в сценарии на рис. 5.39
Напомним: ограничение PRIMARY KEY неявно подразумевает ограничение NOT NULL.
5.2.2.3. Значение по умолчанию. Иногда значение NULL целесообразно заменить некоторым другим предопределенным значением. В таких случаях используют свойство столбца (ограничение целостности) DEFAULT.
На рис. 5.42 приведен пример создания таблицы БД, имеющей столбец FACULTY_ NAME со свойством DEFAULT.
Рис. 5.42. Пример использования ограничения DEFAULT
Результат выполнения SELECT-запроса в сценарии на рис. 5.42 представлен на рис. 5.43.
Рис. 5.43. Результат выполнения SELECT-запроса в сценарии на рис. 5.42
При создании таблицы FACULTY (рис. 5.42) для столбца FACULTY_ NAME с помощью ключевого слова DEFAULT указывается значение, которое будет вводиться в этот столбец оператором INSERT, если в списке столбцов для вводимой строки имя столбца FACULTY_ NAME отсутствует (на рис. 5.42 оператор INSERT c комментарием «неявный ввод значения default»). Кроме того, можно явно указать на необходимость применения значения по умолчанию, как это сделано с помощью оператора INSERT с комментарием «явный ввод значения default».
Следует обратить внимание: свойство DEFAULT не отменяет возможности явного ввода значения NULL.
Операторы UPDATE (рис. 5.42) демонстрируют возможность изменения значений в столбце со свойством DEFAULT на значение NULL и значение по умолчанию.
Допускается комбинация свойств DEFAULT и NOT NULL столбца таблицы БД (рис. 5.44) . В этом случае для такого столбца станет невозможным установить значение NULL.
Рис. 5.44. Комбинация свойств NOT NULL и DEFAULT
5.2.2.4. Внешний ключ. Внешний ключ – ограничение целостности, основанное на асимметричной связи (часто говорят – на отношении), установленной между двумя таблицами БД. Связь может быть установлена между строками таблицы (для краткости FK-таблицы), имеющей внешний ключ (FOREIGN KEY), и строками таблицы (PK-таблицы), имеющей первичный ключ (PRIMARY KEY). Часто говорят, что строки FK-таблицы ссылаются на строки PK-таблицы.
На одну строку PK-таблицы могут ссылаться несколько строк FK-таблицы. Поэтому говорят, что между PK-таблицей и FK-таблицей установлено отношение «один ко многим». Отметим, что PK- и FK-таблицы не обязательно являются разными.
Часто для обозначения такого отношения применяется символьное обозначение 1:* n (читается «один к n»), где символ * указывает на то, что допускается нулевое значение n, или другими словами, в PK-таблице допускаются строки, на которые не ссылается ни одна строка из FK-таблицы.
Ограничение FOREIGN KEY может быть задано в таблице только в том случае, если существует таблица, имеющая соответствующее ограничение PRIMARY KEY (PK-таблица).
На рис. 5.45 представлен пример создания FK-таблицы c именем PULPIT, имеющей внешний ключ.
Рис. 5.45. Пример создания таблицы, имеющей ограничение FOREIGN KEY
Ограничение целостности FOREIGN KEY применяется для столбца FACULTY, ключевое слово REFERENCES указывает на PK-таблицу (в нашем случае – FACULTY) и столбец (FACULTY) со свойством PRIMARY KEY. Для краткости в дальнейшем столбец FK-таблицы, для которого указано свойство FOREIGN KEY, будем называть FK-ключом, a столбец PK-таблицы – PK-ключом.
Структура таблицы FACULTY представлена на рис. 5.44, а на рис. 5.46 – содержимое этой таблицы.
Рис. 5.46. Содержимое PK-таблицы FACULTY
Рассмотрим, каким образом ограничение FOREGN KEY проявляет себя при применении DML-операторов к FK-таблице PULPIT и PK-таблице FACULTY. При этом все сформулированные правила касаются лишь двух пар PK-ключей, PK-таблиц и FK-ключей, FK-таблиц и не принимают во внимание других ограничений, которые могут быть установлены для этих таблиц.
FK-таблица, оператор INSERT. Сценарий на рис. 5.47 выполняет шесть операторов INSERT, один из которых (отмечен комментарием) завершился с ошибкой (рис. 5.48, 5.49).
Рис. 5.47. Добавление строк в FK-таблицу
Рис. 5.48. Сообщение об ошибке при выполнении сценария на рис. 5.47
Рис. 5.49. Результат выполнения SELECT-запроса на рис. 5.47
Сформулируем правила применения оператора INSERT при добавлении строки в FK-таблицу.
1. Оператор INSERT выполнится успешно, если для значения FK-ключа добавляемой строки в PK-таблице есть строка со значением PK-ключа, равным значению FK-ключа.
2. Оператор INSERT выполнится успешно, если значение FK-ключа будет NULL.
FK-таблица, оператор UPDATE. На рис. 5.50 сценарий содержит четыре оператора UPDATE, изменяющих FK-ключ в FK-таблице PULPIT. При выполнении четвертого оператора возникает ошибка с кодом 547 (рис. 5.51, 5.52).
Рис. 5.50. Изменение строк FK-таблицы
Рис. 5.51. Сообщение об ошибке при выполнении сценария на рис. 5.50
Рис. 5.52. Результат выполнения SELECT-запроса на рис. 5.50
Правила применения оператора UPDATE при изменении FK-таблицы следующие.
1. Оператор UPDATE выполнится успешно, если новое значение FK-ключа изменяемой строки таково, что в PK-таблице есть строка со значением PK-ключа, равным значению нового FK-ключа.
2. Оператор UPDATE выполнится успешно, если новое значение FK-ключа будет NULL.
FK-таблица, оператор DELETE. На рис. 5.53 представлен сценарий, состоящий из одного оператора DELETE, удаляющего все строки FK-таблицы PULPIT. На рис. 5.54 отображен результат выполнения сценария.
Рис. 5.53. Удаление строк FK-таблицы
Рис. 5.54. Результат выполнения сценария на рис. 5.53
Правило применения оператора DELETE для удаления строк из FK-таблицы очень простое: оператор DELETE выполняется успешно всегда.
Для демонстрации применения DML-операторов к PK-таблице приведем FK-таблицу PULPIT в состояние, отображенное на рис. 5.55, при этом PK-таблица FACULTY осталась в прежнем состоянии (рис. 5.46).
Рис. 5.55. Содержимое таблицы PULPIT
Заметим следующее:
1) на строку со значением PK-ключа ИДиП PK-таблицы FACULTY ссылается две строки из FK-таблицы PULPIT;
2) на строки со значениями PK-ключей ИЭФ и XT иТ не ссылается ни одна строка из FK-таблицы PULPIT;
3) одна строка из FK-таблицы PULPIT не ссылается ни на одну строку в таблице (значение FK-ключа NULL).
PK-таблица, оператор INSERT. На рис. 5.56 представлен сценарий, состоящий из двух операторов INSERT, добавляющих строки в PK-таблицу FACULTY. При выполнении второго оператора возникла ошибка, связанная с нарушением ограничения PRIMARY KEY (рис. 5.57, 5.58).
Рис. 5.56. Добавление строк в PK-таблицу FACULTY
Рис. 5.57. Сообщение об ошибке при добавлении строки в PK-таблицу FACULTY
Рис. 5.58. Результат выполнения SELECT-запроса в сценарии на рис. 5.56
Правило применения оператора INSERT для добавления строк в PK-таблицу: оператор INSERT выполняется успешно всегда, если не нарушает ограничение PRIMARY KEY.
PK-таблица, оператор UPDATE. На рис. 5.59 представлен сценарий, состоящий из трех операторов UPDATE, изменяющих строки в PK-таблице FACULTY. При выполнении второго и третьего операторов возникли ошибки, связанные с нарушением ограничений FOREIGN KEY и PRIMARY KEY (рис. 5.60, 5.61).
Рис. 5.59. Изменение строк в PK-таблице FACULTY
Рис. 5.60. Сообщение об ошибках при изменении строк в PK-таблице FACULTY
Рис. 5.61. Результат выполнения SELECT-запроса в сценарии на рис. 5.60
Правило применения оператора UPDATE для изменения строк в PK-таблице: оператор UPDATE выполняется успешно, если применяется к строкам, на которые не ссылаются строки из FK-таблицы, и не нарушается ограничение PRIMARY KEY; строки FK-таблицы, не связанные со строками PK-таблицы (имеют значение NULL FK-ключа), никак не влияют на выполнение оператора UPDATE.
PK-таблица, оператор DELETE. На рис. 5.62 представлен сценарий, состоящий из двух операторов DELETE, удаляющих строки в PK-таблице FACULTY. При выполнении второго оператора возникла ошибка, связанная с нарушением ограничения FOREIGN KEY (рис. 5.63, 5.64).
Рис. 5.62. Удаление строк в PK-таблице FACULTY
Рис. 5.63. Сообщение об ошибках при удалении строк в PK-таблице FACULTY
Рис. 5.64. Результаты выполнения SELECT-запросов в сценарии на рис. 5.62
Правило применения оператора DELETE для удаления строк в PK-таблице: оператор DELETE выполняется успешно, если применяется к строкам, на которые не ссылаются строки из FK-таблицы; строки FK-таблицы, не связанные со строками PK-таблицы (имеют значение NULL FK-ключа), никак не влияют на выполнение оператора DELETE.
5.2.2.5. Проверка значений. При вводе или корректировке для проверки значений данных применяется ограничение целостности CHECK. На рис. 5.65 представлен пример использования CHECK для проверки значений в столбце GENDER таблицы TEACHER. После ключевого слова CHECK в описании свойств столбца GENDER следует логическое выражение, проверяющее значение в столбце.
В том случае, когда значение выражения принимает значение «истина», оно считается правильным. Оператор IN, используемый в логическом выражении, имеет значение «истина» в том случае, если значение в столбце, указанном слева от IN, будет совпадать хотя бы с одним значением, указанным в списке справа от IN.
Рис. 5.65. Пример использования ограничения CHECK
В сценарии на рис. 5.65 выполняется пять операторов INSERT и три оператора UPDATE. Два оператора завершаются с ошибкой (рис. 5.66, 5.67), а остальные выполняются успешно.
Рис. 5.66. Сообщения об ошибках в операторах INSERT и UPDATE,
нарушающих ограничение CHECK в таблице TEACHER (рис. 5.65)
Следует обратить внимание на то, что значение NULL не подвергается проверке ограничением CHECK и может быть введено с помощью INSERT либо UPDATE.
Рис. 5.67. Результат выполнения SELECT-запроса в сценарии на рис. 5.65
Ограничение CHECK может быть скомбинировано с другими ограничениями, как это показано на рис. 5.68. В этом случае возможность ввести значение NULL с помощью INSERT или откорректировать в существующей строке значение столбца GENDER на NULL вызовет ошибку.
Рис. 5.68. Совместное использование ограничений
CHECK, NOT NULL и DEFAULT
5.2.2.6. Уникальные значения. Ограничение целостности UNIQUE позволяет значения в столбце или комбинацию значений нескольких столбцов таблицы сделать уникальными в этой таблице. На рис. 5.69 представлен пример использования ограничения UNIQUE для столбца PROFESSION_ NAME таблицы PROFESSION. Сценарий содержит пять операторов INSERT и один оператор UPDATE. Три оператора завершаются с ошибкой, имеющей код 2627 (рис. 5.70, 5.71). Ошибки возникают при повторном вводе значения в столбец PROFESSION_ NAME или корректировке существующего значения на значение, которое уже в этом столбце есть.
Рис. 5.69. Применение ограничения целостности UNIQUE
Рис. 5.70. Сообщения об ошибках в сценарии на рис. 5.69
Рис. 5.71. Результат выполнения SELECT-запроса на рис. 5.69
Следует обратить внимание на то, что ограничение UNIQUE допускает значение NULL, но только в одной из строк, т. е. значение NULL этим ограничением рассматривается как полноправное значение.
Дата: 2019-02-25, просмотров: 194.
