лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
CHAR; для него отсутствующие символы дополняются пробелами справа, т.е. на диск будет всегда записано N символов. Если значение N не указано, то по умолчанию подразумевается 1, т.е. один символ. Как только таблица создана, в нее могут быть помещены данные с помощью оператора INSERT . Сделаем это: 1. INSERT INTO product VALUES 2. ( 'A' , '1232' , 'PC' ) , 3. ( 'A' , '1232' , 'Printer' ) , 4. ( NULL , NULL , NULL ) ; Данные успешно вставлены, но какие-то они неправильные. Во-первых, непонятно чем является модель 1232 – принтером или ПК? Во-вторых, имеется у нас еще одна модель, о которой вообще ничего неизвестно. Здесь следует сделать небольшое отступление, чтобы сказать о том, что, создавая таблицы, мы создаем реляционную модель предметной области. Нашей предметной областью является учет товаров в компьютерной фирме. Чтобы модель данных была адекватна предметной области, требуется так спроектировать таблицы, чтобы то, что происходит с объектами в реальном мире, могло быть отражено в модели, а то, чего не может быть в предметной области, не должно иметь места и в модели. Итак, в реальном мире модель не может быть одновременно и принтером, и ПК, а у нас это получилось. И тут мы приходим к понятию целостности данных и ее реализации посредством ограничений. Категорная целостность или целостность сущностей Категорная целостность означает, что каждый объект, определяемый строкой в таблице, должен быть отличим от любого другого объекта. Иными словами, должен быть такой набор атрибутов, уникальная комбинация значений которого позволит нам отличить один объект от другого. Такой набор атрибутов, однозначно идентифицирующий объект, называется потенциальным ключом. Он не может содержать NULL-значения, поскольку это не даст нам возможности идентифицировать объект (как, скажем, книгу с неизвестным названием). Потенциальных ключей у таблицы может быть несколько. Например, человека можно идентифицировать по номеру паспорта, номеру страхового свидетельства, ИНН, номеру водительских прав и т.д. Для обеспечения категорной целостности в языке SQL существуют спецификации PRIMARY KEY (первичный ключ) и UNIQUE (уникальный ключ). Первичный ключ может быть только один в таблице, уникальных же ключей может быть несколько. Т.е. у нас есть возможность для одного из потенциальных ключей задать спецификацию PRIMARY KEY, а для остальных – UNIQUE. Что в нашем случае может послужить первичным ключом? Поскольку у одного производителя может быть много моделей, и он, соответственно, неоднократно может присутствовать в данных таблицы Product, то столбец maker не может являться кандидатом на роль первичного ключа. Аналогично свойством уникальности не обладает и атрибут type. Уникальным же является номер модели. Это единственный кандидат на роль первичного ключа. Других потенциальных ключей в таблице нет. Чтобы доказать это, можно рассмотреть все остальные комбинации столбцов и показать, что они не гарантируют нам идентификации объекта. В частности, комбинация значений из трех столбцов в приведенном выше примере данных является уникальной, но, тем не менее, не идентифицирует модель 1232. Давайте создадим первичный ключ. В языке SQL есть возможность изменить структуру существующей таблицы при помощи команды ALTER TABLE. Однако давайте будем дозировать новую информацию, поэтому сейчас будет проще пересоздать таблицу, т.е. удалить ее и создать заново с первичным ключом. Удалить таблицу просто (как говорится, ломать – не строить), для этого достаточно выполнить оператор DROP TABLE <имя таблицы>. Итак, 1. DROP TABLE Product; 2. CREATE TABLE Product ( 3. maker varchar ( 10 ) , 4. model varchar ( 50 ) PRIMARY KEY , 5. type varchar ( 50 ) 6. ) ; Мы включили спецификацию первичного ключа в определение столбца. Но можно было это сделать отдельным ограничением: 1. CONSTRAINT <имя ограничения> PRIMARY KEY ( <список столбцов, являющихся первичным ключом> ) При этом наш код будет выглядеть так (опять предварительно удаляем ранее созданную таблицу 1. DROP TABLE Product; 2. CREATE TABLE Product ( 3. maker varchar ( 10 ) , 4. model varchar ( 50 ) , 5. type varchar ( 50 ) , 6. CONSTRAINT product_PK PRIMARY KEY ( model ) 7. ) ; Теперь сама СУБД будет следить за тем, чтобы значения первичного ключа не повторялись и не содержали NULL. Если мы выполним вставку при помощи ранее приведенного оператора INSERT, то получим такое сообщение об ошибке: Violation of PRIMARY KEY constraint 'product_PK'. Cannot insert duplicate key in object 'Product'. The duplicate key value is (1232). (Нарушение ограничения первичного ключа 'product_PK'. Нельзя вставить дубликат ключа в объект 'Product’. Дублирующееся значение ключа (1232).) Если мы исправим ошибку ввода и укажем правильный номер модели принтера: 1. INSERT INTO product VALUES 2. ( 'A' , '1232' , 'PC' ) , 3. ( 'A' , '3001' , 'Printer' ) , 4. ( NULL , NULL , NULL ) ; То получим другую ошибку, связанную с неопределенностью значения первичного ключа: Cannot insert the value NULL into column 'model', table 'Product'; column does not allow nulls. INSERT fails. (Невозможно вставить NULL в столбец 'model', table 'Product'; столбец не допускает NULL-значения. INSERT не выполнен.) Оператор 1. INSERT INTO product VALUES 2. ( 'A' , '1232' , 'PC' ) , 3. ( 'A' , '3001' , 'Printer' ) , 4. ( NULL , '2000' , NULL ) ; отработает без ошибок. Как вы должно быть заметили, в сообщении об ошибке нарушения ограничения первичного ключа фигурирует имя ограничения ('product_PK'). А что будет в случае, если имя не задано? Так было у нас, когда спецификация PRIMARY KEY была включена в определение столбца model. Кстати, во втором варианте мы тоже можем не указывать имя (тогда и ключевое слово CONSTRAINT также опускается 1. DROP TABLE Product; 2. CREATE TABLE Product ( 3. maker varchar ( 10 ) , 4. model varchar ( 50 ) , 5. type varchar ( 50 ) , 6. PRIMARY KEY ( model ) 7. ) ; И так ли важно нам знать имя ограничения? Помимо того, что оно может помочь нам понять причину ошибки, имя требуется при удалении ограничения, когда мы меняем структуру существующей таблицы. Если мы не задаем имя ограничения, СУБД сама присвоит его. И это имя, которое должно быть уникальным в пределах базы данных, мы можем узнать из информационной схемы – стандартного представления метаданных. Например, чтобы узнать имя ограничения первичного ключа, созданного в последнем скрипте, можно выполнить обычный запрос на выборку из таблицы (представления) информационной схемы: 1. SELECT CONSTRAINT_NAME 2. FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 3. WHERE TABLE_NAME= 'Product' AND CONSTRAINT_TYPE = 'primary key' ; У меня это имя получилось таким: PK__Product__0B7E269E30F848ED. У вас, вероятно, будет другим, поскольку его генерирует СУБД. Примером составного ключа может послужить первичный ключ в таблице Outcomes (база данных « Корабли »). Здесь только пара {корабль, сражение} может быть уникальной, поскольку корабль может принять участие в нескольких сражениях, а в одном сражении участвует несколько кораблей. Корабль же в отдельной битве не может быть упомянут дважды, что и запретит сделать первичный ключ. Создать таблицу Outcomes с упомянутым первичным ключом можно следующим образом: 1. CREATE TABLE Outcomes ( 2. ship varchar ( 50 ) , 3. battle varchar ( 20 ) , 4. result varchar ( 10 ) , 5. PRIMARY KEY ( ship, battle ) 6. ) ; Следует заметить, что мы не можем написать так: 1. CREATE TABLE Outcomes ( 2. ship varchar ( 50 ) PRIMARY KEY , 3. battle varchar ( 20 ) PRIMARY KEY , 4. result varchar ( 10 ) 5. ) ; поскольку спецификация PRIMARY KEY может быть только одна. Если первичный ключ может быть только один в таблице, то как быть в том случае, если в нашей модели должны быть уникальны разные комбинации атрибутов? Другими словами, как создать альтернативные ключи, например, если нам потребуется добавить уникальный столбец out_id в таблицу Outcomes? Для этой цели в языке SQL имеется спецификация UNIQUE. Вот так мог бы выглядеть запрос на создание таблицы Outcomes с дополнительным столбцом out_id: 1. CREATE TABLE Outcomes ( 2. ship varchar ( 50 ) , 3. battle varchar ( 20 ) , 4. result varchar ( 10 ) , 5. out_id int, 6. PRIMARY KEY ( ship, battle ) , 7. UNIQUE ( out_id ) 8. ) ; Как уже упоминалось выше, ограничений UNIQUE может быть создано несколько для одной таблицы. Есть еще одно отличие этого ограничения от ограничения PRIMARY KEY. Столбец, на котором создано ограничение UNIQUE, может содержать NULL- значение, но только одно. Вы можете спросить, - как же в этом случае быть с идентификацией объекта? Вспомним пример с книгами, когда мы ищем книгу по уникальному названию. Пусть мы имеем ограничение UNIQUE на столбце названия книги. Поскольку NULL-значение может быть только одно, то найти книгу с неизвестным названием мы можем, исключив все книги с известными названиями. Если же нам потребуется альтернативный ключ, не допускающий NULL- значений, то совместно с UNIQUE мы можем наложить ограничение NOT NULL. К этому мы сейчас и переходим. Проверочные ограничения Проверочное ограничение имеет вид: 1. CHECK ( <предикат> ) Предикат может принимать значения TRUE, FALSE или UNKNOWN. Предикат считается нарушенным, когда он принимает значение FALSE; при этом действие, которое привело к нарушению предиката, не выполняется, и генерируется сообщение об ошибке. Вернемся к нашей проблеме, а именно вставке строки (NULL, '2000', NULL) в таблицу Product. Понятно, что подобных строк следует избегать, т.к. неизвестно, чем является модель 2000 и каким производителем она выпускается. Мы можем использовать предикат IS NOT NULL в проверочных ограничениях для столбцов maker и type: 1. DROP TABLE Product; 2. CREATE TABLE Product ( 3. maker varchar ( 10 ) , 4. model varchar ( 50 ) , 5. type varchar ( 50 ) , 6. CONSTRAINT product_PK PRIMARY KEY ( model ) , 7. CONSTRAINT maker_ch CHECK ( maker IS NOT NULL ) , 8. CONSTRAINT type_ch CHECK ( type IS NOT NULL ) 9. ) ; Теперь при попытке вставить строку (NULL, '2000', NULL) мы получим сообщение об ошибке: Cannot insert the value NULL into column 'model', table 'Product'; column does not allow nulls. INSERT fails. (Значение NULL не может быть вставлено в столбец model таблицы Product; столбец не допускает NULL-значений. INSERT не выполнен) И строка вставлена не будет, вернее, будет отклонен весь оператор INSERT. Заметим, что ограничение NOT NULL (как и PRIMARY KEY для простого ключа) может быть записано непосредственно в определение столбца: 1. DROP TABLE Product; 2. CREATE TABLE Product ( 3. maker varchar ( 10 ) NOT NULL , 4. model varchar ( 50 ) PRIMARY KEY , 5. type varchar ( 50 ) NOT NULL 6. ) ; Более того, любое ограничение уровня столбца можно записать непосредственно в определении столбца. Ниже приводится пример , добавляющий новый столбец (available) в таблицу Product: 1. ALTER TABLE Product ADD available VARCHAR ( 20 ) DEFAULT 'Yes' ; Чтобы ограничить допустимые значения в данном столбце значениями 'Yes' и 'No', этот оператор можно было бы переписать в виде: 1. ALTER TABLE Product ADD available VARCHAR ( 20 ) 2. CHECK ( available IN ( 'Yes' , 'No' )) DEFAULT 'Yes' ; Оператор ALTER TABLE Можно выделить следующие уровни проверочных ограничений: уровень атрибута (столбца), уровень кортежа (строки), уровень отношения (таблицы). В ограничении уровня столбца проверяется значение только одного отдельного столбца, другими словами, в ограничении данного типа имеется ссылка только на один столбец той таблицы, в определении которой содержится данное ограничение. Чтобы привести пример такого ограничения, вернёмся к схеме « Компьютерная фирма ». В таблице Product в столбце type может находиться одно из трех значений. Мы можем запретить ввод любой другой информации в этот столбец при помощи такого ограничения: 1. CHECK ( type IN ( 'printer' , 'pc' , 'laptop' )) Давайте сделаем отступление, чтобы познакомиться с оператором ALTER TABLE, который позволит нам изменять структуру таблицы, не пересоздавая её всякий раз заново. Это тем более важно, что изменение структуры может потребоваться тогда, когда таблица уже содержит данные. С помощью оператора ALTER TABLE можно добавить или удалить столбцы, значения по умолчанию, а также ограничения. В настоящий момент нас интересует добавление ограничения на столбец type, поэтому вначале приведём синтаксис оператора для добавления ограничения: 1. ALTER TABLE <имя таблицы> 2. ADD CONSTRAINT <имя ограничения> <ограничение>; Давайте теперь добавим наше ограничение и проверим, как оно работает. 1. ALTER TABLE Product 2. ADD CONSTRAINT chk_type CHECK ( type IN ( 'pc' , 'laptop' , 'printer' )) ; Чтобы убедиться в том, что ограничение работает как мы того ожидаем, попробуем добавить модель нового типа: 1. INSERT INTO Product VALUES ( 'A' , 1122 , 'notebook' ) ; Как и ожидалось, в ответ мы получим сообщение об ошибке: The INSERT statement conflicted with the CHECK constraint "chk_type". The conflict occurred in database "learn", table "dbo.product", column 'type'. The statement has been terminated. (Конфликт инструкции INSERT с ограничением CHECK "chk_type". Конфликт произошел в базе данных "learn", таблица "dbo.product", столбец 'type'. Выполнение данной инструкции было прервано.) Как легко догадаться, ограничение уровня строки содержит ссылки на несколько столбцов. При этом ограничение проверяется для каждой изменяемой строки отдельно. Строка может быть добавлена (или изменена), если ограничение не нарушено. В качестве примера давайте запретим производителю Z выпускать что-либо помимо принтеров. 1. ALTER TABLE Product 2. ADD constraint chk_maker_Z CHECK (( maker= 'Z' AND type= 'printer' ) OR maker <> 'Z' ) ; Итак, ограничение проверяет, что модель в таблице Product должна быть принтером производителя Z (maker='Z' and type= 'printer') или любого другого производителя (но не Z). Если мы попытаемся добавить модель ПК производителя Z, 1. INSERT INTO Product VALUES ( 'Z' , 1122 , 'PC' ) ; то получим приведенное выше сообщение об ошибке, в котором вместо имени ограничения chk_type будет упомянуто ограничение chk_maker_Z. При этом модель принтера будет добавлена без проблем: 1. INSERT INTO Product VALUES ( 'Z' , 1122 , 'Printer' ) ; Разумеется, другой производитель сможет выпускать все, что угодно: 1. INSERT INTO Product VALUES ( 'V' , 1123 , 'PC' ) ; Значения по умолчанию Для столбца может быть задано значение по умолчанию, т.е. значение, которое будет подставляться в том случае, когда оператор вставки не предоставляет значения для этого столбца. Как правило, значением по умолчанию выбирается наиболее часто встречающееся значение. Пусть для нашей базы данных наибольшая часть моделей представляет собой ПК. Давайте установим для столбца type значение по умолчанию ‘PC’. Добавить значение по умолчанию можно с помощью оператора ALTER TABLE. Согласно стандарту, оператор для нашего примера имел бы вид: 1. ALTER TABLE Product 2. ALTER COLUMN "type" SET DEFAULT 'PC' ; Однако SQL Server не поддерживает в данном случае стандартный синтаксис; в диалекте T-SQL аналогичную операцию можно выполнить так: 1. ALTER TABLE Product 2. ADD DEFAULT 'PC' FOR type; Теперь при добавлении в таблицу Product модели ПК мы можем не указывать тип. 1. INSERT INTO Product ( maker, model ) VALUES ( 'A' , '1124' ) ; Заметим, что значением по умолчанию может быть не только литеральная константа, но и функция без параметров. В частности, мы можем использовать функцию CURRENT_TIMESTAMP, возвращающую текущее значение даты-времени. Давайте добавим столбец в таблицу Product, который будет содержать время, соответствующее выполнению операции добавления модели в БД. 1. ALTER TABLE Product 2. ADD add_date DATETIME DEFAULT CURRENT_TIMESTAMP; Добавим модель 1125 производителя А 1. INSERT INTO Product ( maker, model ) VALUES ( 'A' , '1125' ) ; и посмотрим на результат 1. SELECT * FROM Product WHERE model = '1125' ; |