Как указать вторичный ключ
Перейти к содержимому

Как указать вторичный ключ

  • автор:

Как указать вторичный ключ

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

Общий синтаксис установки внешнего ключа на уровне столбца:

[FOREIGN KEY] REFERENCES главная_таблица (столбец_главной_таблицы) [ON DELETE ] [ON UPDATE ]

Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY , но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE .

Общий синтаксис установки внешнего ключа на уровне таблицы:

FOREIGN KEY (стобец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE ] [ON UPDATE ]

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date );

Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

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

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса «FK_»:

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

В данном случае ограничение внешнего ключа CustomerId называется «FK_Orders_To_Customers».

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции:

  • CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
  • NO ACTION : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
  • SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
  • SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.

Каскадное удаление

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

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE )

Аналогично работает выражение ON UPDATE CASCADE . При изменении значения первичного ключа автоматически изменится значение связанного с ним внешнего ключа. Но так как первичные ключи, как правило, изменяются очень редко, да и с принципе не рекомендуется использовать в качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );

Установка значения по умолчанию

CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT )

Как указать вторичный ключ

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

Общий синтаксис установки внешнего ключа на уровне таблицы:

[CONSTRAINT имя_ограничения] FOREIGN KEY (столбец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE действие] [ON UPDATE действие]

Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет внешний ключ. А после ключевого слова REFERENCES указывается имя связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES идут выражения ON DELETE и ON UPDATE , которые задают действие при удалении и обновлении строки из главной таблицы соответственно.

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLE Customers ( Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Phone VARCHAR(20) NOT NULL UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:

CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );

ON DELETE и ON UPDATE

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

  • CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
  • SET NULL : при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL . (В этом случае столбец внешнего ключа должен поддерживать установку NULL)
  • RESTRICT : отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.
  • NO ACTION : то же самое, что и RESTRICT .
  • SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.

Каскадное удаление

Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы. Для этого применяется опция CASCADE :

CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE );

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

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

CREATE TABLE Orders ( Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );

FOREIGN KEY в SQL

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

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

SQL поддерживает ссылочную целостность с ограничением FOREIGN KEY. Эта функция должна ограничивать значения, которые можно ввести в базу данных, чтобы заставить внешний ключ и родительский ключ соответствовать принципу ссылочной целостности. Одно из действий ограничения FOREIGN KEY — это отбрасывание значений для полей, ограниченных как внешний ключ, который еще не представлен в родительском ключе. Это ограничение также воздействует на способность изменять или удалять значения родительского ключа.

Ограничение FOREIGN KEY используется в команде CREATE TABLE (или ALTER TABLE (предназначена для модификации структуры таблицы), содержащей поле, которое объявлено внешним ключом. Родительскому ключу дается имя, на которое имеется ссылка внутри ограничения FOREIGN KEY.

Подобно большинству ограничений, оно может быть ограничением таблицы или столбца, в форме таблицы позволяющей использовать многочисленные поля как один внешний ключ. Синтаксис ограничения таблицы FOREIGN KEY: FOREIGN KEY REFERENCES [ ] Первый список столбцов — это список из одного или более столбцов таблицы, которые отделены запятыми и будут созданы или изменены этой командой. Pktable — это таблица содержащая родительский ключ. Она может быть таблицей, которая создается или изменяется текущей командой. Второй список столбцов — это список столбцов, которые будут составлять родительский ключ. Списки двух столбцов должны быть совместимы, т.е.:

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

FOREIGN KEY SQL. Пример 1

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Kod_spec integer NOT NULL, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), FOREIGN KEY (Kod_spec) REFERENCES Spec (Kod_spec) );

При использовании ALTER TABLE вместо CREATE TABLE, для применения ограничения FOREIGN KEY, значения, указываемые во внешнем ключе и родительском ключе, должны быть в состоянии ссылочной целостности. Иначе команда будет отклонена.

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

FOREIGN KEY. Пример 2

CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal), Kod_spec integer REFERENCES Spec );

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

Этого не достаточно для родительского ключа в случае выполнения такого требования, как при объявлении внешнего ключа. SQL должен быть уверен, что двойные значения или пустые значения (NULL) не были введены в родительский ключ. Следовательно необходимо убедиться, что все поля, которые используются как родительские ключи, имеют или ограничение PRIMARY KEY или ограничение UNIQUE, наподобие ограничения NOT NULL.

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

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

FOREIGN KEY. Пример 3

CREATE TABLE payment ( sh_payout integer, sh_eml integer, date_payout date, summ_payout real, FOREIGN KEY (sh_eml) REFERENCES k_sotr2 (eid) );

В данном примере FOREIGN KEY столбец sh_eml связывается со столбцом eid из таблицы k_sotr2.

Основные ключевые слова, используемые в статье:

foreign key, foreign key mysql, foreign key oracle, foreign key sql, foreign key references

Как указать вторичный ключ

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

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

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

Добавление внешнего ключа
Чтобы добавить внешний ключ в существующую таблицу базы данных:

1. Перейдите к необходимому диспетчеру для записи ThingWorx , связанной с таблицей базы данных. Эти диспетчеры установлены в таблице ManagerConfigurationSettings на странице Конфигурация вещи конфигурации точки запуска ( PTC.Factory.C_LaunchPointConfigurationThing_[ReleaseVersion] ). Например, диспетчером для записей Operator Advisor является PTC.SCA.SCO.DefaultProductionOrderManager .

2. В разделе Сервисы найдите и переопределите сервис GetDBInfo сущности, к которой добавляется внешний ключ. Например, для добавления внешнего ключа в таблицу базы данных для порядков работы, необходимо найти и переопределить сервис GetJobOrderDBInfo .

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

4. Добавьте массив foreignKeys в профиль данных со следующими свойствами:

◦ identifier : наименование сущности в базе данных. Если значение не указано, то система автоматически создает его в формате _ _fk . Указанное значение должно быть уникальным в рамках указанных и созданных автоматически значений. Значение не должно превышать максимальную длину, разрешенную для идентификаторов базы данных.

◦ name : наименование поля, которое должно быть внешним ключом в текущем профиле данных. Базовый тип поля внешнего ключа должен соответствовать базовому типу ссылочного поля. Например, если базовый тип ссылочного поля STRING , то поле со значением внешнего ключа должно быть также базового типа STRING .

◦ referenceDataShapeName : профиль данных ссылочной таблицы базы данных.
◦ referenceFieldName : имя поля, содержащего ссылочное значение.
◦ onDelete : влияет на экземпляры текущего профиля данных при удалении ссылочного профиля данных.

◦ deleteReference : влияет на экземпляры ссылочного профиля данных при удалении текущего профиля данных.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *