Понимание SQL
Шрифт:
Вышеупомянутое определяет Customers.snum как внешний ключ у которого родительский ключ - это Salespeople.snum. Это эквивалентно такому ограничению таблицы:
FOREIGN KEY (snum) REGERENCES Salespeople (snum)
Используя ограничение FOREIGN KEY таблицы или столбца, вы можете не указывать список столбцов родительского ключа если родительский ключ имеет ограничение PRIMARY KEY. Естественно, в случае ключей со многими полями, порядок столбцов во внешних и первичных ключах должен совпадать, и, в любом случае, принцип совместимости между двумя ключами все еще применим. Например, если мы поместили ограничение PRIMARY KEY в поле snum таблицы Продавцов, мы могли бы использовать его как внешний ключ в таблице Заказчиков (подобно
CREATE TABLE Customers
( cnum integer NOT NULL PRIMARY KEY,
cname char(10),
city char(10),
snum integer REFERENCES Salespeople);
Это средство встраивалось в язык, чтобы поощрять вас использовать первичные ключи в качестве родительских ключей.
Поддержание справочной целостности требует некоторых ограничений на значения, которые могут быть представлены в полях, обьявленных как внешний ключ и родительский ключ. Родительский ключ должен быть структурен, чтобы гарантировать, что каждое значение внешнего ключа будет соответствовать одной указанной строке. Это означает, что он (ключ) должен быть уникальным и не содержать никаких пустых значений(NULL).
Этого не достаточно для родительского ключа в случае выполнения такого требования как при объявлении внешнего ключа. SQL должен быть уверен что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно вы должны убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобии ограничения NOT NULL.
Ссылка ваших внешних ключей только на первичные ключи, как мы это делали в типовых таблицах, - хорошая стратегия. Когда вы используете внешние ключи, вы связываете их не просто с родительскими ключами на которые они ссылаются; вы связываете их с определенной строкой таблицы где этот родительский ключ будет найден. Сам по себе родительский ключ не обеспечивает никакой информации которая бы не была уже представлена во внешнем ключе. Смысл, например, поля snum как внешнего ключа в таблице Заказчиков - это связь которую он обеспечивает, не к значению поля snum на которое он ссылается, а к другой информации в таблице Продавцов, такой например как, имена продавцов, их местоположение, и так далее. Внешний ключ - это не просто связь между двумя идентичными значениями; это - связь, с помощью этих двух значений, между двумя строками таблицы указанной в запросе.
Это поле snum может использоваться чтобы связывать любую информацию в строке из таблицы Заказчиков со ссылочной строкой из таблицы Продавцов - например чтобы узнать - живут ли они в том же самом городе, кто имеет более длинное имя, имеет ли продавец кроме данного заказчика каких-то других заказчиков, и так далее.
Так как цель первичного ключа состоит в том, чтобы идентифицировать уникальность строки, это более логичный и менее неоднозначный выбор для внешнего ключа. Для любого внешнего ключа который использует уникальный ключ как родительский ключ, вы должны создать внешний ключ который бы использовал первичный ключ той же самой таблицы для того же самого действия. Внешний ключ который не имеет никакой другой цели кроме связывания строк, напоминает первичный ключ используемый исключительно для идентификации строк, и является хорошим средством сохранить структуру вашей базы данных ясной и простой, и - следовательно создающей меньше трудностей.
Внешний ключ, в частности, может содержать только те значения которые фактически представлены в родительском ключе или пустые(NULL). Попытка ввести другие значения в этот ключ будет отклонена.
Вы можете обьявить внешний ключ как NOT NULL, но это необязательно, и в большинстве случаев, нежелательно. Например, предположим, что вы вводите заказчика не зная заранее, к какому продавцу он будет назначен. Лучший выход в этой ситуации, будет если использовать значение NOT NULL, которое должно быть изменено позже на конкретное значение.
ЧТО СЛУЧИТСЯ, ЕСЛИ ВЫ ВЫПОЛНИТЕ КОМАНДУ МОДИФИКАЦИИ
Давайте условимся, что все внешние ключи созданые в наших таблицах примеров, обьявлены и предписаны с ограничениями внешнего ключа, следующим образом:
CREATE TABLE Salespeople
(snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL,
city char(10),
comm decimal);
CREATE TABLE Customers
(cnum integer NOT NULL PRIMARY KEY,
cname char(10) NOT NULL,
city char(10),
rating integer,
snum integer,
FOREIGN KEY (snum) REFERENCES Salespeople,
UNIQUE (cnum, snum) ;
CREATE TABLE Orders
(cnum integer NOT NULL PRIMARY KEY,
amt decimal,
odate date NOT NULL,
cnum integer NOT NULL
snum integer NOT NULL
FOREIGN KEY (cnum, snum) REFERENCES
CUSTOMERS (cnum, snum);
Имеется несколько атрибутов таких определений о которых нужно поговорить. Причина по которой мы решили сделать поля cnum и snum в таблице Порядков, единым внешним ключом - это гарантия того, что для каждого заказчика содержащегося в порядках, продавец кредитующий этот порядок - тот же что и указаный в таблице Заказчиков. Чтобы создать такой внешний ключ, мы были бы должны поместить ограничение таблицы UNIQUE в два поля таблицы Заказчиков, даже если оно необязательно для самой этой таблицы. Пока поле cnum в этой таблица имеет ограничение PRIMARY KEY, оно будет уникально в любом случае, и следовательно невозможно получить еще одну комбинацию поля cnum с каким-то другим полем.
Создание внешнего ключа таким способом поддерживает целостность базы данных, даже если при этом вам будет запрещено внутреннее прерывание по ошибке и кредитовать любого продавца, иного чем тот который назначен именно этому заказчику. С точки зрения поддержания целостности базы данных, внутренние прерывания (или исключения ) конечно же нежелательны. Если вы их допускаете и в то же время хотите поддерживать целостность вашей базы данных, вы можете обьявить поля snum и cnum в таблице Порядков независимыми внешними ключами этих полей в таблице Продавцов и таблице Заказчиков, соответственно. Фактически, использование поля snum в таблице Порядков, как мы это делали, необязательно, хотя это полезно было сделать для разнообразия. Поле cnum связывая каждый порядок заказчиков в таблице Заказчиков, в таблице Порядков и в таблице Заказчиков, должно всегда быть общим чтобы находить правильное поле snum для данного порядка (не разрешая никаких исключений).
Это означает что мы записываем фрагмент информации - какой заказчик назначен к какому продавцу - дважды, и нужно будет выполнять дополнительную работу чтобы удостовериться, что обе версии согласуются. Если мы не имеем ограничения внешнего ключа как сказано выше, эта ситуация будет особенно проблематична, потому что каждый порядок нужно будет проверять вручную (вместе с запросом ), чтобы удостовериться что именно соответствующий продавец кредитовал каждую соответствующую продажу.
Наличие такого типа информационной избыточности в вашей базе данных, называется деморализация (denormalization ), что не желательно в идеальной реляционной базе данных, хотя практически и может быть разрешена. Деморализация может заставить некоторые запросы выполняться быстрее, поскольку запрос в одной таблице выполняется всегда значительно быстрее чем в обьединении.
Как такие ограничения воздействуют на возможность и невозможность Вами использовать команды модификации DML? Для полей, определенных как внешние ключи, ответ довольно простой: любые значения которые вы помещаете в эти поля с командой INSERT или UPDATE должны уже быть представлены в их родительских кючах. Вы можете помещать пустые(NULL) значения в эти поля, несмотря на то что значения NULL не позволительны в родительских ключах, если они имеют ограничение NOT NULL. Вы можете удалять (DELETE ) любые строки с внешними ключами не используя родительские ключи вообще. Поскольку затронут вопрос об изменении значений родительского ключа, ответ, по определению ANSI, еще проще, но возможно несколько более ограничен: любое значение родительского ключа ссылаемого с помощью значения внешнего ключа, не может быть удалено или изменено. Это означает, например, что вы не можете удалить заказчика из таблицы Заказчиков пока он еще имеет порядки в таблице Порядков. В зависимости от того, как вы используете эти таблицы, это может быть или желательно или хлопотно. Однако - это конечно лучше чем иметь систему, которая позволит вам удалить заказчика с текущими порядками и оставить таблицу Порядков ссылающейся на несуществующих заказчиков.