Coздание таблицы, ссылающейся на саму себя

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

 

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

 

INSERT INTO Employees

(EMPLOYEE_ID,

FIRST_NAME,

LAST_NAME,

EMAIL,

HIRE_DATE,

JOB_ID,

SALARY,

MANAGER_ID

)

VALUES

(997,

'Boson',

'Billy Bob',

'BBB@mail.',

'010911',

'IT_PROG',

10000,

)

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

 

ALTER TABLE Employees

ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")

REFERENCES "EMPLOYEES" ("EMPLOYEE_ID")

 

В данном операторе есть только одно отличие от оператора CREATE. Но есть и еще один нюанс, состоящий в том, что в данном определении допускается не исполь­зовать ключевое слово FOREIGN KEY (но этого не следует делать) и оставлять только конструкцию REFERENCES. К этому времени таблица Employees уже определена, но если бы речь шла о ее создании с самого начала, то на данном этапе можно было бы применить следующий сценарий (особого внимания заслуживает определение внеш­него ключа на столбце Manager_ID):

 

CREATE TABLE "EMPLOYEES"

("EMPLOYEE_ID" NUMBER(6,0),

"FIRST_NAME" VARCHAR2(20),

"LAST_NAME" VARCHAR2(25)

CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,

"EMAIL" VARCHAR2(25)

CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,

"PHONE_NUMBER" VARCHAR2(20),

"HIRE_DATE" DATE

CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,

"JOB_ID" VARCHAR2(10)

CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,

"SALARY" NUMBER(8,2),

"COMMISSION_PCT" NUMBER(2,2),

"MANAGER_ID" NUMBER(6,0),

"DEPARTMENT_ID" NUMBER(4,0),

 

CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY

("EMPLOYEE_ID") ENABLE,

 

CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY

("MANAGER_ID")

REFERENCES "EMPLOYEES" ("EMPLOYEE_ID") ENABLE

)

 

Следует отметить, что при попытке уничтожить таблицу Employees в данный момент (что­бы выполнить оператор, рассматриваемый во втором примере) было бы получено сообще­ние об ошибке. Это связано с тем, что после определения в таблице Orders ссылки на таблицу Employees эти две таблицы становятся, как принято выражаться, "связанными со схемой". Это означает, что в базе данных теперь содержится информация о наличии так называемой зависимости другой таблицы от таблицы Employees. С учетом такой инфор­мации СУБД Oracle не позволяет уничтожать таблицу, на которую ссылается другая таблица. Чтобы получить возможность выполнить с помощью СУБД Oracle удаление таблицы Employees, необходимо вначале уничтожить внешний ключ в таблице DEPARTMENTS.

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

 

Ограничения unique

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

После того как будет задано ограничение UNIQUE, должно соблюдаться такое усло­вие, чтобы все значения в столбцах, указанных в этом ограничении, были уникальны­ми. При обнаружении попытки обновить или вставить строку со значением, которое уже имеется в столбце с ограничением уникальности, СУБД Oracle вырабатывает сообщение об ошибке и отклоняет попытку ввести такую строк).

В отличие от ограничения первичного ключа, применение ограничения unique не приводит к тому, что автоматически исключается возможность вставить в соответствующий столбец NULL-значение. В данном случае возможность применения NULL-значений зависит от того, как задана опция null для указанного столбца таблицы. Но следует учитывать, что даже если вставка NULL-значений разрешена, фактически допускается вставка в столбец толь­ко одного такого значения (хотя одно NULL-значение не равно другому NULL-значению, с точки зрения применения ограничения unique эти значения все равно рассматриваются как дубликаты).

Итак, к описанию данного ограничения больше нельзя что-либо добавить (к тому же при его изучении применим основной объем сведений, касающихся первичных ключей), поэтому приступим непосредственно к изучению примеров кода. Для это­го создадим еще одну таблицу в базе данных Accounting, но на этот раз назовем ее COUNTRIES:

 

CREATE TABLE "COUNTRIES"

("COUNTRY_ID" CHAR(2)

CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE,

"COUNTRY_NAME" VARCHAR2(40),

"REGION_ID" NUMBER,

CONSTRAINT " COUNTRY _ COUN_NAME _UK"

UNIQUE ("COUNTRY_NAME ") ENABLE

)

 

 

Создание ограничений unique на существующих таблицах

Как уже было сказано, ограничение UNIQUE действует в основном по таким же принципам, как ограничения первичных и внешних ключей. Рассмотрим следующий пример создания ограничения UNIQUE на таблице Employees:

 

ALTER TABLE Employees

ADD CONSTRAINT "EMP_EMAIL_AK" UNIQUE ("EMAIL")

 

 

Отметим, что в приведенном выше примере аббревиатура АК, используемая в имени ограни­чения, расшифровывается как Alternate Key (альтернативный ключ). С другой стороны, для обозначения первичных ключей и внешних ключей применяются аббревиатуры РК (Primary Key) и FK (Foreign Key). А в именах ограничений UNIQUE часто используется префикс UK, или просто U.

 

Ограничения check

Удобным свойством ограничений CHECK является то, что эти ограничения не обя­зательно должны применяться только к какому-то конкретному столбцу. Безусловно, указанные ограничения могут относиться лишь к некоторому столбцу, но также до­пускается их распространение по существу на всю таблицу, в том смысле, что с их помощью может осуществляться проверка значений в одном столбце на основании значений другого столбца (при условии, что все эти столбцы принадлежат к одной и той же таблице, а значения берутся из одной и той же обновляемой или вставляемой строки). С помощью ограничений CHECK может также осуществляться проверка того, соответствует ли некоторое сочетание значений столбцов заданному критерию.

Ограничения CHECK определяются на основании таких же правил, которые рас­пространяются на операции проверки, используемые в конструкции WHERE. Примеры критериев, которые могут применяться в ограничении CHECK, приведены в таблице 2.

 

Таблица 13 - Примеры применения ограничения CHECK

Назначение	Код SQL
Обеспечение применения в столбце Month только допустимых значений	between I and 12
Правильное форматирование номера карточки социального обеспечения	Like ‘ [0-9] [0-9] [0-9]-[0-9] [0-9]-[0-9] [0-9] ‘
Регламентация списка допустимых значений в поле Shippers	in (‘UPS’, ‘Fed Еx’, ‘USPS’)
Регламентация применения только положительных значений цены	UnitPrice >= О
Ссылка на другое поле в той же строке	ShipDate >= OrderDate

 

В действительности в таблице 13 приведена лишь незначительная часть всех возмож­ных примеров, поскольку количество вариантов применения различных операций сравнения в конструкции CHECK является практически бесконечным. В ограничении CHECK может быть задано почти любое такое же выражение, которое допускается за­давать в конструкции WHERE. К тому же ограничения CHECK позволяют достичь гораз­до более высокой производительности по сравнению с альтернативными средствами проверки допустимости данных (правилами и триггерами).

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

 

ALTER TABLE EMPLOYEES

ADD CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0))

Теперь попытаемся выполнить вставку строки со значением, нарушающим ограни­чение CHECK; эта попытка должна привести к возникновению ошибки(рис.1):

INSERT INTO Employees

(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,

JOB_ID,SALARY,MANAGER_ID)

VALUES

(997,'Boson','Billy Bob','BBB@mail.','010911','IT_PROG',-10000,100)

 

 

Рисунок №72 - Ошибка проверки ограничения Check

 

Если после этого будет внесено такое исправление, чтобы данные, вводимые в столбец Salary, соответствовали критерию, заданному в ограничении CHECK, то попытка выполнить оператор INSERT завершится успешно.

 

Ограничения default

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

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

- Значения, подстановка которых должна быть выполнена по умолчанию с по­мощью конструкции DEFAULT, используются только в операторах INSERT, а в случае их определения в операторах UPDATE и DELETE игнорируются.

- Если в операторе INSERT для столбца с конструкцией DEFAULT задано какое-либо значение, то предусмотренное по умолчанию значение не используется.

- Если же для такого столбца значение не задано, то всегда используется значе­ние, предусмотренное по умолчанию.

Следует подчеркнуть, что предусмотренные по умолчанию значения предназначе­ны для использования только в операторах INSERT. Но практика показывает, что для многих начинающих разработчиков программ для СУБД Oracle причина такого положения дел остается непонятной. Тем не менее такая организация работы вполне обоснована — в то время, как происходит вставка строки в таблицу, СУБД Oracle может использовать только те значения столбцов, которые приведены в операторе INSERT (если таковые имеются), или значения, заданные по умолчанию. Если же для какого-либо столбца в операторе вставки не указано ни то ни другое, то СУБД Oracle вставляет в соответствующий столбец таблицы NULL-значение (которое по су­ществу рассматривается как неопределенное значение), а если в определении столб­ца имеется конструкция NOT NULL, то СУБД Oracle отвергает попытку вставки этой строки. Тем не менее после выполнения оператора вставки строки в таблице уже имеется значение в столбце, для которого задана конструкция DEFAULT, а в опе­раторе UPDATE содержатся новые значения. Если в операторе обновления не пред­усмотрено новое значение для столбца с конструкцией DEFAULT, то СУБД Oracle оставляет неизменным то значение, которое уже находится в столбце.

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

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