• Что такое объекты базы данных?
• Что такое схема?
• Таблица как основная форма хранения данных
• Поля и столбцы
• Строки
• Оператор CREATE TABLE
• Ключевое слово STORAGE
• Соглашения о присвоении имен
• Команда ALTER TABLE
• Модификация элементов таблицы
• Добавление столбцов, требующих обязательного ввода данных
• Изменение столбцов
• Создание таблицы на основе уже существующей
• Удаление таблиц
• Условия целостности
• Ключевые поля
• Требования уникальности
• Внешние ключи
• Атрибут NOT NULL
• Использование условий проверки
• Удаление условий
• Резюме
• Вопросы и ответы
• Практикум
• Тесты
• Упражнения

На этом уроке мы обсудим объекты базы данных: узнаем, что они из себя представляют, как они работают, в каком виде хранятся и как связаны друг с другом. Объекты базы данных составляют основу реляционной базы данных. Эти объекты представляют собой логически цельные единицы, используемые для хранения информации, и поэтому на них ссылаются как на объекты базы данных нижнего уровня (back-end database). Большинство из приводимых в тексте этого урока инструкций относится к таблицам, но существуют и другие объекты базы данных, обсуждение которых предполагается по ходу изучения материала книги в других уроках.

Основными на этом уроке будут следующие темы.

• Знакомство с объектами базы данных
• Знакомство со схемами
• Структура таблиц и работа с ними
• Обсуждение природы и атрибутов таблиц
• Примеры создания таблиц и работа с ними
• Обсуждение опций, связанных с хранением данных в таблицах
• Концепции ссылочной целостности и совместимости данных

  Что такое объекты базы данных?

Объект базы данных — это любой определенный в базе данных объект, используемый для хранения данных или ссылок на них. К объектам базы данных относятся, например, таблицы, представления, группы (кластеры), последовательности, индексы и синонимы. В течение этого урока в фокусе нашего внимания будут таблицы, поскольку они представляют собой простейшую форму хранения данных в реляционной базе данных.

  Что такое схема?

Схема — это набор объектов базы данных (в ходе текущего урока — таблиц), ассоциированных с одним конкретным именем пользователя базы данных. Пользователь называется владельцем схемы или, иначе, владельцем связанной группы объектов. Вы можете иметь одну или несколько схем в базе данных. Как правило, любой создающий объекты пользователь создает при этом и свою схему. Схема может состоять как из одной таблицы, так и из множества объектов — ограничений практически нет, если только такие ограничения не предполагает конкретная реализация SQL.

Предположим, администратор выдал вам имя пользователя базы данных и пароль. Пусть это имя USER1. Предположим также, что вы подключились к базе данных и создали таблицу с именем EMPLOYEE_TBL. Тогда настоящим именем вашей таблицы будет USER1. EMPLOYEEJTBL. Именем схемы для этой таблицы будет имя пользователя USER1, который является владельцем схемы. Таким образом вы создадите свою первую схему.

Польза схем проявляется в том, что при доступе к таблице из вашей схемы (т. е. схемы, владельцем которой вы являетесь) вам не нужно ссылаться на имя схемы. Так, вы можете сослаться на свою таблицу одним из следующих способов:

EMPLOYEEJTBL USER1.EMPLOYEEJTBL

Первый вариант предпочтительнее из-за меньшего объема. При необходимости доступа к вашей таблице другим пользователям придется указать имя вашей схемы:

USER:.EMPLOYEEJTBL

Из урока 20. "Создание и использование представлений и синонимов", вы узнаете о распределении привилегий доступа среди других пользователей так, чтобы они имели возможность обратиться к вашим таблицам. Вы узнаете также о синонимах, которые дают возможность назначить таблицам другие имена, чтобы избавиться от необходимости каждый раз указывать имя схемы. На рис. 3.1 показаны две схемы в реляционной базе данных.

Рис. 3.1. Схемы в базе данных

На рис. 3.1 показаны два пользователя, USER1 и USER2, имеющие в базе данных свои таблицы. Каждому из пользователей назначена своя схема. Вот примеры того, как эти пользователи могут обращаться к своим и к чужим таблицам: Доступ LJSER1 к своей таблице tablel: TABLE 1 Доступ USER1 к своей таблице test: TEST Доступ USER1 к таблице tablelO пользователя USER2: USER2.TABLE10 Доступ USER1 к таблице test пользователя USER2: USER2.TEST

Оба пользователя имеют по таблице с именем TEST. Таблицы, принадлежащие разным схемам в базе данных, могут иметь одинаковые имена. Если посмотреть на имена с точки зрения схем, имена таблиц в базе данных всегда будут уникальными, поскольку на самом деле частью имени таблицы всегда является имя владельца схемы. В данном случае, например, имя USER1.TEST отличается от имени USER2.TEST. Если при обращении к таблице по имени вы не укажете имя схемы, сервер базы данных будет искать таблицу только в рамках той схемы, владельцем которой вы являетесь по умолчанию. Например, если USER1 попытается обратиться к TEST, сервер базы данных сначала попытается найти таблицу с именем TEST среди таблиц пользователя USER1 и только потом обратится к другим объектам пользователя USER1, включая и синонимы для таблиц из других схем. Использование синонимов объясняется в ходе урока 21, "Работа с системным каталогом".

Любой сервер баз данных имеет свои правила назначения имен объектов и элементов этих объектов, таких как поля. Правила и соглашения для назначения имен можно уточнить ъ справочной системе каждой конкретной реализации

  Таблица как основная форма хранения данных

Таблица является основной формой хранения данных в реляционной базе данных. Таблица состоит из строк и столбцов, содержащих данные. Таблица физически занимает место в базе данных и может быть как постоянной, так и временной.

  Поля и столбцы

Поле, называемое также столбцом в реляционной базе данных, является частью таблицы, которой приписан определенный тип данных. Имя поля должно соответствовать типу данных, которые будут вводиться в столбец. Столбцы могут быть помечены как NULL или NOT NULL. В столбец, которому назначено NOT NULL, обязательно должны быть введены какие-нибудь данные. Если же столбец определен как NULL, данные в него вводить не обязательно.

Каждая таблица базы данных должна содержать хотя бы один столбец. Столбцы являются теми элементами таблицы, в которых хранятся данные конкретных типов, например, имя персоны или телефонный номер. Так, один из столбцов в таблице с информацией о клиентах может быть предназначен для хранения имени клиента.

Вообще говоря, имя должно быть одной непрерывной строкой. Как правило, имя объекта должно представлять собой одну непрерывную строку с ограниченным числом символов в ней, зависящим от конкретной реализации SQL. Для разделения слов, из которых складывается имя, обычно используют символ подчеркивания. Например, столбец для хранения имени клиента лучше назвать CUSTOMER_NAME, a Не CUSTOMERNAME.

Не забудьте ознакомиться с правилами присвоения имен объектам и другим элементам базы данных, которые требует ваша конкретная реализация SQL.

  Строки

Строка представляет запись в таблице базы данных. Например, строка данных в таблице с информацией о клиентах может состоять из кода клиента, его имени, адреса, номера телефона, факса и т. п. Строка состоит* из данных всех полей, относящихся к одной записи в таблице. Таблица может состоять всего из одной строки, а может содержать и миллионы строк данных (записей).

  Оператор CREATE TABLE

Оператор CREATE TABLE, очевидно, используется для того, чтобы создавать таблицы. Хотя непосредственно создание таблицы оказывается совсем простым делом, прежде, чем использовать оператор CREATE TABLE, нужно со всей тщательностью подойти к вопросу планирования структуры таблицы.

Вот несколько простых вопросов, на которые при создании таблицы нужно получить ответы.

• Какого типа данные будут вводиться в таблицу?

• Каким должно быть имя таблицы?

• Каким столбцом (или столбцами) будет задаваться ключевое поле (составной ключ)?

• Какие имена следует присвоить столбцам (полям)?

• Какие типы данных следует назначить столбцам?

• Какой выбрать длину каждого из столбцов?

• Какие столбцы таблицы будут требовать обязательного ввода данных?

Получив ответы на все эти вопросы, не составляет труда построить подходящий оператор CREATE TABLE и применить его.

Синтаксис оператора для создания таблиц будет следующим:

CREATE TABLE ИМЯ_ТАБЛИЦЫ ( ПОЛЕ1 ТИП ДАННЫХ [ NOT NULL ] , ПОЛЕ2 ТИП ДАННЫХ [ NOT NULL ] , ПОЛЕЗ ТИП ДАННЫХ [ NOT NULL ] , ПОЛЕ4 ТИП ДАННЫХ [ NOT NULL ] , ПОЛЕ5 ТИП ДАННЫХ [ NOT NULL ] ) ;

В примерах этого урока используются такие популярные типы данных, как CHAR (символьный постоянной длины), VARCHAR (символьный переменной длины), NUMBER (числовой, для десятичных и не десятичных значений) и DATE (для значений даты и времени).

Создайте таблицу с именем EMPLOYEE_TBL, например, с помощью следующего оператора:

CREATE TABLE EMPLOYEE_TBL

(EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR2(40) NOT NULL,

EMP_ST_ADDR VARCHAR2(20) NOT NULL,

EMP_CITY VARCHAR2U5) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMBER(5) NOT NULL,

EMP_PHONE NUMBER(10) NULL,

EMP_PAGER NUMBER(10) NULL);

В результате в таблице будет восемь столбцов. Обратите внимание на использование символа подчеркивания в именах столбцов так, что эти имена кажутся состоящими из нескольких слов (например, EMPLOYEE ID превратилось в EMP_ID). Каждому столбцу назначен свой тип данных определенной длины, а использование ограничений NULL /NOT NULL позволяет указать, какие из столбцов должны обязательно иметь значения во всех строках таблицы. Поле EMP_PHONE определено как NULL, и это значит, что для соответствующего столбца допустимы пустые значения (ввиду того, что не все могут иметь телефонные номера). Информация о столбцах разделяется запятыми, а описания всех столбцов заключены в круглые скобки (открывающая скобка помещена перед определением первого столбца, а закрывающая — после определения последнего).

Завершается оператор точкой с запятой. Большинство реализаций SQL предполагает использование некоторого символа, означающего завершение оператора или передачу оператора серверу базы данных. В Oracle используется точка с запятой. Trans-act-SQL использует оператор GO. В этой книге используется точка с запятой.

В созданной нами таблице каждая запись или строка с данными будет состоять из следующих полей: EMP_ID, EMP_NAME, EMP__ST_ADDR, EMP_CITY, EMP_ST, EMP_ZIP, EMP_PHONE, EMP_PAGER

В этой таблице каждое поле является столбцом. Столбец EMP_ID может состоять как из одного-единственного табельного номера одного служащего, так и из множества табельных номеров многих служащих в зависимости от требований запроса к базе данных или транзакций.

Значение NULL является значением столбца по умолчанию, следовательно, это значение в операторе CREATE TABLE вводить не обязательно.

  Ключевое слово STORAGE

В определенных формах ключевое слово STORAGE поддерживается большинством реализаций SQL. С помощью ключевого слова STORAGE в операторе CREATE TABLE можно задать исходные размеры таблицы и, как правило, при создании таблицы оно используется. Вот пример использования ключевого слова STORAGE в одной из реализаций SQL:

CREATE TABLE EMPLOYEEJTBL (EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR2(40) NOT NULL,

EMP_ST_ADDR VARCHAR2(20) NOT NULL,

EMP_CITY VARCHAR2CL5) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMBER(5) NOT NULL,

EMP_PHONE NUMBER(10) NULL,

EMP_PAGER NUMBER(10) NULL) STORAGE

(INITIAL 3K NEXT 2К );

В некоторых реализациях ключевое слово STORAGE может иметь несколько опций. С помощью INITIAL выделяется объем памяти в байтах, килобайтах и т. д., который должна изначально занять таблица. NEXT задает приращение объема памяти, которая должна быть выделена таблице, если таблица вырастет настолько, что выйдет за пределы изначально выделенного ей объема. Вы обнаружите и другие опции ключевого слова STORAGE и имейте в виду, что эти опции варьируют от реализации к реализации. Если ключевое слово STORAGE пропущено, то в большинстве реализаций SQL используются значения по умолчанию, что обычно оказывается не очень хорошим решением для приложений.

Обратите внимание на полученный с помощью отступов аккуратный вид приведенного выше примера использования оператора CREATE TABLE. Это облегчает чтение и отладку программ.

Ключевое слово STORAGE в различных реализациях SQL используется по-разному. Предыдущий пример использования STORAGE взят из Oracle, где оно добавляется к оператору CREATE TABLE. He забывайте, что стандарт ANSI для SQL является всего лишь стандартом и не более. Стандарт непосредственно языком не является, а обеспечивает производителям рекомендации, касающиеся разработки их реализаций SQL. Вы обнаружите, что и типы данных тоже зависят от реализации. Во многих отношениях зависят от реализации и подходы к хранению и обработке данных.

  Соглашения о присвоении имен

При выборе имен для объектов, в частности для таблиц и столбцов, имя должно соответствовать хранящимся данным. Например, для таблицы, в которой предполагается хранить информацию о служащих, подойдет имя EMPLOYEE_TBL. Той же логике должны следовать и имена столбцов. Для столбца, в котором будет храниться номер телефона служащего, очевидно подходящим именем будет PHONE_NUMBER.

Выясните, какие ограничения на длину имен и допустимые символы имеет ваша реализация SQL — эти ограничения для разных реализаций различны.

  Команда ALTER TABLE

Таблицу можно модифицировать и после ее создания с помощью команды ALTER TABLE. С помощью этой команды можно добавлять и удалять столбцы, менять определения столбцов, добавлять и удалять ограничения, а в некоторых реализациях и модифицировать значения, задаваемые ключевым словом STORAGE. Стандартный синтаксис команды ALTER TABLE следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ [MODIFY] [COLUMN ИМЯ_СТОЛБЦА] [ТИП ДАННЫХ|NULL NOT NULL] [RESTRICT|CASCADE]

[DROP] [CONSTRAINT ИМЯ_ОГРАНИЧЕНИЯ]

[ADD] [COLUMN] ОПРЕДЕЛЕНИЕ СТОЛБЦА

  Модификация элементов таблицы

Атрибуты столбца задают правила представления данных в столбце. С помощью команды ALTER TABLE можно менять атрибуты столбца. Под атрибутами здесь понимается следующее:

• тип данных в столбце;

• длина, точность или масштаб данных в столбце;

. • разрешение или запрет иметь в столбце значение NULL.

В следующем примере команда ALTER TABLE используется для того, чтобы изменить атрибуты столбца EMP_ID таблицы EMPLOYEE_TBL.

ALTER TABLE EMPLOYEE_TBL MODIFY (EMP_ID VARCHAR2(10));

Изменение таблицы.

Столбцу уже был назначен тип данных VARCHAR2 (строка символов переменной длины), но здесь была увеличена максимальная длина строки с 9 до 10.

  Добавление столбцов, требующих обязательного ввода данных

При добавлении столбца в уже существующую таблицу с имеющимися в ней данными новому столбцу нельзя назначить атрибут NOT NULL. NOT NULL означает, что столбец должен содержать значения для каждой строю! в таблице, так что если добавляемый столбец получит атрибут NOT NULL, вы сразу же получите противоречие с этим ограничением, поскольку имеющиеся в таблице столбцы не имеют значений для нового столбца.

И все же имеется возможность добавить столбец, требующий обязательного ввода данных, следующим образом.

1. Добавьте столбец, задав ему атрибут NULL (это значит, что в столбце не обязательно должны присутствовать данные). 

2. Введите данные в каждую строку нового столбца таблицы.

3. Убедившись, что столбец содержит значение в каждой из строк таблицы, можно изменить атрибут столбца на NOT NULL.

  Изменение столбцов

При изменении столбцов таблиц нужно учитывать целый ряд моментов. Общие правила следующие.

• Ширина столбца может быть увеличена до максимальной длины, разрешенной для соответствующего типа данных.

• Ширину столбца можно уменьшить только до наибольшей длины имеющихся в этом столбце значений.

• Для столбцов с числовыми данными ширину всегда можно увеличить.

• Для столбцов с числовыми данными ширину можно уменьшить только тогда, когда нового числа знаков будет достаточно для размещения любого из имеющихся в столбце значений.

• Для числовых данных можно увеличивать или уменьшать число десятичных знаков.

• Тип данных в столбце обычно можно изменить.

В некоторых реачизациях использование определенных опций оператора ALTER TABLE может быть запрещено. Например, вам могут не позволить удалять столбцы из таблиц. Вместо этого вам нужно будет удалить таблицу и создать новую с нужным числом столбцов. Могут возникнуть проблемы с удалением столбцов из таблицы, зависящей от столбца из другой таблицы, или с удалением столбца, на который ссылается другая таблица. По этому поводу внимательно просмотрите документацию, предлагаемую той реализацией SQL, с которой вы работаете.

  Создание таблицы на основе уже существующей

С помощью комбинации операторов CREATE TABLE и SELECT можно создать копию уже существующей таблицы. Столбцы новой таблицы будут иметь те же определения. При этом для копирования можно выбрать как все столбцы, так и только некоторые.

Новые столбцы, создаваемые как функции или комбинации столбцов, автоматически учитывают размеры, необходимые для хранения имеющихся данных. Базовый синтаксис оператора создания таблицы из уже существующей будет следующим.

CREATE TABLE ИМЯ_НОВОЙ_ТАБЛИЦЫ AS SELECT [ *|СТОЛБЕЦ!, СТОЛБЕЦ2 ] FROM ИМЯ_ТАБЛИЦЫ [ WHERE ]

Обратите здесь внимание на новые ключевые слова, в частности, на ключевое слово SELECT, которое представляет запрос к базе данных и будет обсуждаться подробно позже. Но сейчас важно знать, что вы можете создавать таблицы, основываясь на результатах запроса.

Сначала выполним запрос, чтобы увидеть данные в таблице PRODUCTSJTBL.

SELECT * FROM PRODUCTSJTBL;

PROD_ID PROD_DESC COST

11235 КОСТЮМ ВЕДЬМЫ 29.99

222 ПЛАСТИКОВЫЕ ТЫКВЫ 7.75

13 ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 1.1

90 ФОНАРИ 14.5

15 КОСТЮМЫ В АССОРТИМЕНТЕ 10

9 СЛАДКАЯ КУКУРУЗА 1.35

6 ТЫКВЕННЫЕ КОНФЕТЫ 1.45

87 ПЛАСТИКОВЫЕ ПАУКИ 1.05

119 МАСКИ В АССОРТИМЕНТЕ 4.95

SELECT * выбирает данные из всех полей данной таблицы Символ * представляет целую сроку данных (т. е. запись) в таблице.

Затем на основе результатов этого запроса создадим таблицу с именем PRODUCTS_TMP.

CREATE TABLE PRODUCTS_TMP AS

SELECT * FROM PRODUCTS_TBL;

Создание таблицы.

Если теперь выполнить запрос к таблице PRODUCTS_TMP, результат будет выглядеть так же, как результат запроса коригинальной таблице.

  SELECT * FROM PRODUCTS_TMP;

PROD_ID PROD_DESC COST

11235 КОСТЮМ ВЕДЬМЫ 29.99

222 ПЛАСТИКОВЫЕ ТЫКВЫ 7.75

13 ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 1.1

90 ФОНАРИ 14.5

15 КОСТЮМЫ В АССОРТИМЕНТЕ 10

9 СЛАДКАЯ КУКУРУЗА 1.35

6 ТЫКВЕННЫЕ КОНФЕТЫ 1.45

87 ПЛАСТИКОВЫЕ ПАУКИ 1.05

119 МАСКИ В АССОРТИМЕНТЕ 4.95

При создании таблицы из уже имеющейся, новая таблица получит те же атрибуты STORAGE, что и исходная.

  Удаление таблиц

Удаление таблиц является, пожалуй, самым простым делом. Если используется опция RESTRICT либо на таблицу ссылается представление или ограничение, используемый для удаления оператор DROP возвратит ошибку. При использовании опции CASCADE будет выполнено удаление не только самой таблицы, но и всех ссылающиеся на таблицу представлений и ограничений. Синтаксис оператора, используемого для удаления таблиц, следующий:

DROP TABLE ИМЯ_ТАБЛЙЦЫ [ RESTRICT|CASCADE ]

В следующем примере удаляется только что созданная нами таблица.

DROP TABLE USER1.PRODUCTSJTMP;

Удаление таблицы.

 При удалении таблицы всегда указывайте имя схемы или владельца таблицы. Иначе вы рискуете удалить не ту таблицу Если вы имеете несколько имен пользователей, под которыми вам разрешен доступ к базе данных, убедитесь, что вы вошли в базу данных под нужным именем

  Условия целостности

УСЛОВИЯ целостности обеспечивают правильность и согласованность данных в реляционных базах данных. В основе целостности данных в реляционных базах данных лежит понятие ссылочной целостности. Ссылочная целостность складывается из целого ряда условий целостности, каждое из которых играет свою роль.

  Ключевые поля

Ключевое поле или ключ (primary key) — это термин, используемый для обозначения столбца или нескольких столбцов, однозначно идентифицирующих каждую строку в таблице. Обычно ключ задается одним столбцом в таблице, но можно задать и сложный ключ на основе комбинации значений нескольких столбцов. Например в таблице с информацией о служащих логично выбрать в качестве ключевых полей столбец с идентификационным кодом служащего или столбец с присвоенным служащему табельным номером. Целью является наличие для каждой записи в таблице уникального ключа, подобного персональному идентификационному коду. Поскольку в таблице с информацией о служащих скорее всего не должно быть более одной записи для каждого из служащих, табельный номер служащего будет вполне подходящим ключом. Ключ таблице назначается при ее создании.

В следующем примере ключевым полем в таблице EMPLOYEEJIBL назначается поле EMP_ID:

CREATE TABLE EMPLOYEEJTBL (EMP_ID CHAR(9) NOT NULL PRIMARY KEY,

EMPJSIAME VARCHAR2(40) NOT NULL,

EMP_ST_ADDR VARCHAR2(20) NOT NULL,

EMP_CITY VARCHAR2(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMBER(5) NOT NULL,

EMP_PHONE NUMBER(10) NULL,

EMP_PAGER NUMBER(10) NULL);

В этом случае ключ задается при создании таблицы и является по своей сути ограничивающим условием. Можно задать ключ и непосредственно как ограничивающее условие, например, следующим образом.

CREATE TABLE EMPLOYEEJTBL

(EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR2(40) NOT NULL,

EMP_ST_ADDR VARCHAR2(20) NOT NULL,

EMP_CITY VARCHAR2(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMBER(5) NOT NULL,

EMP_PHONE NUMBER(10) NULL,

EMP_PAGER NUMBER(10) NULL, PRIMARY KEY (EMP_ID));

В этом примере офаничивающее условие ключа указано в операторе CREATE TABLE через запятую после определения всех столбцов таблицы.

Ключ, составленный из нескольких столбцов, можно задать одним из следующих способов.

CREATE TABLE PRODUCTS (PROD_ID VARCHAR2(10) NOT NULL,

VEND_ID VARCHAR2(10) NOT NULL,

PRODUCT VARCHAR2(30) NOT NULL,

COST NUMBER(8,2) NOT NULL, PRIMARY KEY (PROD__ID, VEND_ID));

ALTER TABLE PRODUCTS "

ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID, VEND_ID);

  Требования уникальности

Ограничивающее требование уникальности для столбца в таблице подобно ключу в том смысле, что значение в соответствующем столбце должно быть уникальным для каждой строки. Назначив один столбец ключевым, вы можете задать требование уникальности для другого, хотя последний и не будет использоваться в качестве ключа.

Рассмотрим следующий пример.

CREATE TABLE EMPLOYEE_TBL

(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,

EMP_NAME VARCHAR2(40) NOT NULL,

EMP_ST_ADDR VARCHAR2(20) NOT NULL,

EMP_CITY VARCHAR2(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMBER(5) NOT NULL,

EMP_PHONE NUMBER(10) NULL UNIQUE,

EMP_PAGER NUMBER(IO) NULL);

Ключевым в данном примере является EMP_ID и это значит, что столбец с табельным номером служащего будет использоваться для того, чтобы обеспечить уникальность всех записей в таблице. Именно на ключевой столбец обычно ссылаются в за-

просах или при связывании таблиц. Столбцу EMP_PHONE назначен атрибут UNIQUE, и это значит, что никакие двое служащих не должны иметь одинаковые телефонные номера. Между этими двумя атрибутами нет большой разницы за исключением того, что ключ используется для упорядочения данных таблицы и для связывания таблиц.

  Внешние ключи

Внешний ключ (foreign key) — это столбец в дочерней таблице, ссылающий на ключ родительской таблицы. Использование внешних ключей является основным механизмом поддержания ссылочной целостности внутри реляционной базы данных. Столбец, назначенный внешним ключом, используется для ссылок на столбец, определенный как ключ в другой таблице. Рассмотрим пример создания внешнего ключа.

CREATE TABLE EMPLOYEE_PAY_TBL (EMP_ID CHAR(9) NOT NULL,

POSITION VARCHAP2(15) NOT NULL,

DATE_HIRE DATE NULL,

PAY_RATE NUMBER(4,2) NOT NULL,

DATE_LAST_RAISE DATE NULL,

CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_!D, REFERENCES EMPLOYEE_TBL (EMP_ID));

Столбец EMP_ID в этом примере назначается внешним ключом таблицы EMPLOYEE PAY_TBL Это! внешний ключ ссылается на столбец EMP_ID таблицы EMPLOYEE_TBL. Наличие внешнего ключа гарантирует, что для, каждого значения ЕМР ID в таблице EMFLOYEE_PAY_TBL найдется соогве!С1вующее значение EMP_ID в таблице EMPLOYEE_TBL. Такую связь называют родителъско-дочерним отношением. Родительской таблицей является таблица EMPLOYEE_TBL, а дочерней — EMPLOYEE_PAY_TBL. Чтобы лучше понять суть родительско-дочерних отношений между таблицами, рассмотрите рис. 3.2.

Рис. 3.2. Родительско-дочерние отношения между таблицами

На этом рисунке столбец EMP_ID дочерней таблицы ссылается на столбец EMP_ID родительской таблицы. Некоторое значение можно будет ввести в столбец EMP_ID дочерней таблицы только тогда, когда такое же значение существует в столбце EMP_ID родительской таблицы. Точно так же некоторое значение можно будет удалить из столбца EMP_ID родительской таблицы только тогда, когда все соответствующие значения уже удалены из столбца EMP_ID дочерней таблицы. Так осуществляется ссылочная целостность.

Внешний ключ можно назначить таблице с помощью команды ALTER TABLE, как показано в следующем примере.

ALTER TABLE EMPLOYEE_PAY_TBL

ADD CONSTRAINT ID_FK FOREIGN KEY (EMP_ID)

REFERENCES EMPLOYEE_TBL (EMP_ID);

В разных реализациях SQL команда ALTER TABLE имеет разные опции — это, в частности, относится и к опциям, задающим ограничивающие условия. Кроме того, варьируются сама форма задания ограничений, но лежащая в ее основе концепция ссылочной целостности должна быть неизменной для всех реляционных баз данных.

  Атрибут NOT NULL

В предыдущих примерах ключевые слова NULL и NOT NULL использовались во всех строках с определениями столбцов после указания типа данных. Атрибут NOT NULL — это ограничение, которое можно назначить столбцу в таблице. Это ограничение не позволяет оставлять столбцы пустыми. Другими словами, для столбца, помеченного как NOT NULL, требуется наличие данных во всех строках таблицы. Если атрибут NOT NULL не назначен для столбца, для такого столбца значением по умолчанию обычно является NULL, что позволяет иметь пустые, значения в столбце.

  Использование условий проверки

УСЛОВИЯ проверки можно использовать для проверки правильности вводимых в столбец данных. Условия проверки используются для организации редактирования данных в базе данных на нижнем уровне, хотя довольно часто редактирование данных бывает организовано на уровне приложений. Вообще говоря, обычно при редактировании имеются ограничения на вводимые в таблицы или другие объекты данные, или на уровне самой базы данных или на уровне приложения, доступного пользователю. Условия проверки обеспечивают дополнительный уровень защиты данных.

Использование условий проверки показано в следующем примере.

CREATE TABLE EMPLOYEE_TBL (EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR2(40) NOT NULL,

EMP_ST_ADDR VARCHAR2{20) NOT NULL,

EMP_CITY VARCHAR2(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMBER(5) NOT NULL,

EMP_PHONE NUMBER(10) NULL,

EMP_PAGER NUMBER(10) NULL), PRIMARY KEY (EMP_ID), CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP = '46234' );

В этом примере условия проверки назначены столбцу EMP_ZIP и состоят в том, чтобы у всех служащих из этой таблицы был ZIP-код равный ' 46234 '. Наверное, это слишком строгое ограничение, но зато вы можете видеть, как оно работает.

Если нужно использовать условия проверки для того, чтобы допустить ZIP-коды только из определенного набора значений, условие может выглядеть так:

CONSTRAINT CHK_EMP_ZIP CHECK (EMP_ZIP in ('46234','46227','46745'));

Если имеется минимальный уровень оплаты труда, который должен быть обеспечен служащему, можно указать следующие условия:

CREATE TABLE EMPLOYEE_PAY_TBL

(EMP_ID CHAR(9) NOT NULL,

POSITION VARCHAR2(15) NOT NULL,

DATE_HIRE DATE NULL,

PAY_RATE NUMBER(4,2) NOT NULL,

DATE_LAST_RAISE DATE NULL,

CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEEJTBL (EMP_ID), CONSTRAINT CHK_PAY CHECK (PAY_RATE > 12.50 ) );

В данном примере любому из служащих, информация о которых заносится в таблицу, необходимо назначить 'плату более $12,50 в час. Для условий проверки можно использовать практически любые условия, допустимые для запросов SQL. В ходе дальнейшего чтения такие условиях будут обсуждаться подробнее.

  Удаление условий

Любое из назначенных условий можно удалить с помощью оператора ALTER TABLE с опцией DROP CONSTRAINT. Например, чтобы отменить назначение ключа в таблице EMPLOYEES, можно воспользоваться следующей командой.

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

Изменение таблицы.

В некоторых реализациях SQL предлагаются специальные сокращения для удаления определенных условий. Например, чтобы удалить ключ таблицы в Oracle, можно воспользоваться следующей командой.

ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

Изменение таблицы.

В некоторых реализациях SQL вместо окончательного удаления ограничения из базы данных можно временно отключить имеющееся ограничение, чтобы позже включить его снова.

  Резюме

Вы узнали кое-что об объектах базы данных, в частности, достаточно подробно разобрались с таблицами. Таблицы представляют собой простейшую форму хранения данных в реляционных базах данных. В таблицах сгруппированы логически единые куски информации, например, о служащих, клиентах или товарах. Таблицы состоят из столбцов, которым назначаются различные атрибуты, в основном, типы данных и различные ограничения — значения NOT NULL, ключевые поля и внешние ключи, требования уникальности значений.

Вы ознакомились с примерами использования команды CREATE TABLE и с ее опциями, среди которых, в частности, параметры выделения таблице памяти. Вы научились модифицировать структуру уже созданной таблицы с помощью команды ALTER

TABLE. Хотя процесс управления таблицами базы данных и нельзя отнести к основным задачам использования SQL, мы считаем, что если вы узнаете больше о структуре и природе таблиц, вам легче будет освоить принципы доступа к таблицам, лежащие в основе манипуляций данными или осуществления запросов. Из следующих уроков вы узнаете об управлении с помощью SQL другими объектами базы данных, в частности, индексами таблиц и представлениями.

  Вопросы и ответы

При создании таблицы обязательно ли в ее имени использовать суффикс _твь?

Определенно нет. Вас ничего не принуждают его использовать. Например, таблице с информацией о служащих можно назначить либо одно из следующих имен, либо любое другое, которое будет соответствовать хранимым в этой таблице данным:

EMPLOYEE EiMP_TBL EMPLOYEE TBL EMPLOYEE^TABLE WORKER

Почему при удалении таблицы так важно указывать имя соответствующей схемы?

Вот вам непридуманная история о молодом администраторе базы данных, удалившем таблицу. Один программист создал в рамках своей схемы таблицу с именем точно таким же, как у таблицы с производственной информацией. Прошло некоторое время и он из компании уволился. При попытке ликвидации его учетной записи оператор DROP USER вернул ошибку из-за каких-то принадлежащих программисту объектов, оставшихся в базе данных. Исследование проблемы показало, что таблица, созданная тем программистом, не нужна и по отношению к ней был применен оператор DROP TABLE.

Все прошло прекрасно, но возникла другая проблема — оказалось, что администратор применил оператор DROP TABLE, войдя в базу данных под именем производственной схемы. Как было бы хорошо, если бы администратор указал имя схемы или владельца удаляемой таблицы¹ Да, была удалена не та таблица из не той схемы. На восстановление производственной базы данных потребовалось почти восемь часов.

  Практикум

Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".

  Тесты

1. Будет ли работать следующий оператор CREATE TABLE? Если нет, то что нужно в нем исправить?

CREATE TABLE EMPLOYEEJTBL AS (SSN NUMBER(9) NOT NULL,

LAST_NAME VARCHAR2(20) NOT NULL

FIRST_NAME VARCHAR2(20) NOT NULL,

MIDDLE_NAME VARCHAR2(20) NOT NULL, ST ADDRESS VARCHAR2(30) NOT NULL, CITY CHAR(20) NOT NULL, STATE CHAR2) NOT

 NULL, ZIP NUMBER(4) NOT NULL, DATE HIRED DATE) STORAGE

(INITIAL 3K, NEXT IK ) ;

2. Можно ли удалить столбец из таблицы?

3. Что будет, если в оператор CREATE TABLE не включить ключевое слово STORAGE?

  Упражнения

1. Ознакомьтесь с Приложением В, "Операторы CREATE TABLE для примеров книги" и проанализируйте приведенные там операторы.