Как установить внешний ключ microsoft sql

Изменение связей по внешнему ключу

Вы можете изменить сторону внешнего ключа связи в SQL Server с помощью SQL Server Management Studio или Transact-SQL. При изменении внешнего ключа таблицы изменяются столбцы, связанные со столбцами таблицы первичного ключа.

В этом разделе

Перед началом работыОграниченияБезопасность
Изменение внешнего ключа с использованием следующих средств:Среда SQL Server Management StudioTransact-SQL

Перед началом

Ограничения

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

Столбец типа char или sysname можно связать со столбцом типа varchar .
Столбец типа binary можно связать со столбцом типа varbinary .
Псевдоним типа данных можно связать со своим базовым типом.

Безопасность

Разрешения

Требуется разрешение ALTER на таблицу.

Использование среды SQL Server Management Studio

Изменение внешнего ключа

Разверните в обозревателе объектовтаблицу с внешним ключом, а затем разверните Ключи.
Щелкните правой кнопкой мыши внешний ключ, который нужно изменить, и выберите пункт Изменить.
В диалоговом окне Связи внешних ключей можно внести следующие изменения. Выбранные связи
Выводит список существующих связей. Выберите связь, чтобы ее свойства отобразились в сетке справа. Если этот список пуст, то для этой таблицы не было определено ни одной связи. Добавление
Создает новую связь. Спецификации таблиц и столбцов должны быть заданы, иначе связь будет недопустима. Удаление
Удаляет связь, выбранную в списке Выбранные связи . Чтобы отменить добавление связи, удалите эту связь, нажав данную кнопку. Общая категория
Разверните, чтобы увидеть категории Проверить существующие данные при создании или повторном включении и Спецификации таблиц и столбцов. Проверить существующие данные при создании или повторном включении
Проверяет все существующие данные в таблице перед созданием или возобновлением ограничения относительно этого ограничения. Категория спецификации таблиц и столбцов
Разверните, чтобы увидеть, какие столбцы, из каких таблиц действуют как внешний и первичный (или уникальный) ключ в данной связи. Для изменения или задания этих значений нажмите кнопку с многоточием ( . ) справа от поля свойства. Базовая таблица внешнего ключа
Показывает, какая таблица содержит столбец, действующий как внешний ключ в выбранной связи. Внешние ключевые столбцы
Показывает, какой столбец действует как внешний ключ в выбранной связи. Базовая таблица первичного или уникального ключа
Показывает, какая таблица содержит столбец, действующий как первичный (или уникальный) ключ в выбранной связи. Первичные или уникальные ключевые столбцы
Показывает, какой столбец действует как первичный (или уникальный) ключ в выбранной связи. Категория «Идентификатор»
Разверните, чтобы увидеть поля свойств Имя и Описание. Название
Показывает имя связи. Если создается новая связь, ей присваивается имя по умолчанию в зависимости от таблицы, отображаемой в активном окне в Конструкторе таблиц. Имя можно изменить в любой момент. Описание
Описывает связь. Чтобы ввести более подробное описание, щелкните Описание и нажмите кнопку с многоточием (. ) справа от поля свойства. При этом появится большее поле для записи текста. Категория конструктора таблиц
Разверните, чтобы увидеть данные для категорий Проверка существующих данных при создании и возобновлении и Включить использование для репликации. Принудительное применение для репликации
Показывает, использовать ли данное ограничение, когда агент репликации выполняет в таблице вставку, изменение или удаление. Принудительное использование ограничения внешнего ключа
Укажите, допустимы ли изменения данных столбцов связи, если при этом нарушится целостность связи внешнего ключа. Выберите Да , если нужно запретить такие изменения, и Нет , если нужно разрешить их. Категория спецификаций INSERT и UPDATE
Разверните, чтобы увидеть сведения о Правиле удаления и Правиле обновления связи. Удаление правила
Укажите, что произойдет при попытке пользователя удалить строку с данными, участвующую в связи внешнего ключа:
- Нет действий. Сообщение об ошибке информирует пользователя, что удаление недопустимо, и инструкция DELETE откатывается.
- Каскад. Удаляет все строки, содержащие данные, участвующие в связи внешнего ключа. Не следует использовать параметр CASCADE, если таблица будет включена в публикацию слиянием, в которой используются логические записи.
- Присвоить NULL . Задает значение, равное NULL, если все внешние ключевые столбцы в таблице могут содержать значения NULL.
- Присвоить значение по умолчанию . Задает значение по умолчанию, определенное для данного столбца, если все внешние ключевые столбцы в таблице имеют значения по умолчанию.

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

Нет действий. Сообщение об ошибке информирует пользователя, что обновление недопустимо, и инструкция UPDATE откатывается.
Каскад. Обновляет все строки, содержащие данные, участвующие в связи внешнего ключа. Не следует использовать параметр CASCADE, если таблица будет включена в публикацию слиянием, в которой используются логические записи.
Присвоить NULL . Задает значение, равное NULL, если все внешние ключевые столбцы в таблице могут содержать значения NULL.
Присвоить значение по умолчанию. Задает значение по умолчанию, определенное для данного столбца, если все внешние ключевые столбцы в таблице имеют значения по умолчанию.

Использование Transact-SQL

Изменение внешнего ключа

Чтобы изменить ограничение FOREIGN KEY с помощью Transact-SQL, сначала необходимо удалить существующее ограничение FOREIGN KEY, а затем повторно создать его с новым определением. Дополнительные сведения см. в разделах Delete Foreign Key Relationships и Create Foreign Key Relationships.

Создание, изменение и удаление внешних ключей

В объектах управления SQL Server (SMO) внешние ключи представлены ForeignKey объектом.

Чтобы создать в SMO внешний ключ, необходимо указать таблицу, в которой внешний ключ определен в конструкторе объекта ForeignKey. В этой таблице надо выбрать хотя бы один столбец, который будет внешним ключом. Для этого создайте объектную переменную ForeignKeyColumn и укажите имя столбца, который станет внешним ключом. Теперь укажите таблицу и столбец, на которые будут выполняться ссылки. Add Используйте метод, чтобы добавить столбец в свойство объекта Column.

Столбцы, представляющие внешний ключ, перечислены в свойстве ForeignKey объекта Columns объекта объекта. Первичный ключ, на который ссылается внешний ключ, представлен свойством ReferencedKey, которое находится в таблице, указанной в свойстве ReferencedTable.

пример

Чтобы использовать какой-либо из представленных примеров кода, нужно выбрать среду, шаблон и язык программирования, с помощью которых будет создаваться приложение. Дополнительные сведения см. в статье «Создание проекта SMO Visual C# в Visual Studio .NET».

Создание, изменение и удаление внешнего ключа на языке Visual Basic

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

'Connect to the local, default instance of SQL Server. Dim srv As Server srv = New Server 'Reference the AdventureWorks2022 database. Dim db As Database db = srv.Databases("AdventureWorks2022") 'Declare a Table object variable and reference the Employee table. Dim tbe As Table tbe = db.Tables("Employee", "HumanResources") 'Declare another Table object variable and reference the EmployeeDepartmentHistory table. Dim tbea As Table tbea = db.Tables("EmployeeDepartmentHistory", "HumanResources") 'Define a Foreign Key object variable by supplying the EmployeeDepartmentHistory as the parent table and the foreign key name in the constructor. Dim fk As ForeignKey fk = New ForeignKey(tbea, "test_foreignkey") 'Add BusinessEntityID as the foreign key column. Dim fkc As ForeignKeyColumn fkc = New ForeignKeyColumn(fk, "BusinessEntityID", "BusinessEntityID") fk.Columns.Add(fkc) 'Set the referenced table and schema. fk.ReferencedTable = "Employee" fk.ReferencedTableSchema = "HumanResources" 'Create the foreign key on the instance of SQL Server. fk.Create()

Создание, изменение и удаление внешнего ключа на языке Visual C#

Создание, изменение и удаление внешнего ключа в PowerShell

# Set the path context to the local, default instance of SQL Server and to the #database tables in AdventureWorks2022 CD \sql\localhost\default\databases\AdventureWorks2022\Tables\ #Get reference to the FK table $tbea = get-item HumanResources.EmployeeDepartmentHistory # Define a Foreign Key object variable by supplying the EmployeeDepartmentHistory # as the parent table and the foreign key name in the constructor. $fk = New-Object -TypeName Microsoft.SqlServer.Management.SMO.ForeignKey ` -argumentlist $tbea, "test_foreignkey" #Add BusinessEntityID as the foreign key column. $fkc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.ForeignKeyColumn ` -argumentlist $fk, "BusinessEntityID", "BusinessEntityID" $fk.Columns.Add($fkc) #Set the referenced table and schema. $fk.ReferencedTable = "Employee" $fk.ReferencedTableSchema = "HumanResources" #Create the foreign key on the instance of SQL Server. $fk.Create()

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

В этом примере демонстрируются следующее:

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

Читать:

Сколько стоит ipad с apple pencil

Версия на языке C#:

// compile with: // /r:Microsoft.SqlServer.Smo.dll // /r:microsoft.sqlserver.management.sdk.sfc.dll // /r:Microsoft.SqlServer.ConnectionInfo.dll // /r:Microsoft.SqlServer.SqlEnum.dll using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Sdk.Sfc; using Microsoft.SqlServer.Management.Common; using System; public class A < public static void Main() < Server svr = new Server(); Database db = new Database(svr, "TESTDB"); db.Create(); // PK Table Table tab1 = new Table(db, "Table1"); // Define Columns and add them to the table Column col1 = new Column(tab1, "Col1", DataType.Int); col1.Nullable = false; tab1.Columns.Add(col1); Column col2 = new Column(tab1, "Col2", DataType.NVarChar(50)); tab1.Columns.Add(col2); Column col3 = new Column(tab1, "Col3", DataType.DateTime); tab1.Columns.Add(col3); // Create the ftable tab1.Create(); // Define Index object on the table by supplying the Table1 as the parent table and the primary key name in the constructor. Index pk = new Index(tab1, "Table1_PK"); pk.IndexKeyType = IndexKeyType.DriPrimaryKey; // Add Col1 as the Index Column IndexedColumn idxCol1 = new IndexedColumn(pk, "Col1"); pk.IndexedColumns.Add(idxCol1); // Create the Primary Key pk.Create(); // Create Unique Index on the table Index unique = new Index(tab1, "Table1_Unique"); unique.IndexKeyType = IndexKeyType.DriUniqueKey; // Add Col1 as the Unique Index Column IndexedColumn idxCol2 = new IndexedColumn(unique, "Col2"); unique.IndexedColumns.Add(idxCol2); // Create the Unique Index unique.Create(); // Create Table2 Table tab2 = new Table(db, "Table2"); Column col21 = new Column(tab2, "Col21", DataType.NChar(20)); tab2.Columns.Add(col21); Column col22 = new Column(tab2, "Col22", DataType.Int); tab2.Columns.Add(col22); tab2.Create(); // Define a Foreign Key object variable by supplying the Table2 as the parent table and the foreign key name in the constructor. ForeignKey fk = new ForeignKey(tab2, "Table2_FK"); // Add Col22 as the foreign key column. ForeignKeyColumn fkc = new ForeignKeyColumn(fk, "Col22", "Col1"); fk.Columns.Add(fkc); fk.ReferencedTable = "Table1"; // Create the foreign key on the instance of SQL Server. fk.Create(); // Get list of Foreign Keys on Table2 foreach (ForeignKey f in tab2.ForeignKeys) < Console.WriteLine(f.Name + " " + f.ReferencedTable + " " + f.ReferencedKey); >// Get list of Foreign Keys referencing table1 foreach (Table tab in db.Tables) < if (tab == tab1) continue; foreach (ForeignKey f in tab.ForeignKeys) < if (f.ReferencedTable.Equals(tab1.Name)) Console.WriteLine(f.Name + " " + f.Parent.Name); >> > >

Версия на языке Visual Basic:

' compile with: ' /r:Microsoft.SqlServer.Smo.dll ' /r:microsoft.sqlserver.management.sdk.sfc.dll ' /r:Microsoft.SqlServer.ConnectionInfo.dll ' /r:Microsoft.SqlServer.SqlEnum.dll Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Sdk.Sfc Imports Microsoft.SqlServer.Management.Common Public Class A Public Shared Sub Main() Dim svr As New Server() Dim db As New Database(svr, "TESTDB") db.Create() ' PK Table Dim tab1 As New Table(db, "Table1") ' Define Columns and add them to the table Dim col1 As New Column(tab1, "Col1", DataType.Int) col1.Nullable = False tab1.Columns.Add(col1) Dim col2 As New Column(tab1, "Col2", DataType.NVarChar(50)) tab1.Columns.Add(col2) Dim col3 As New Column(tab1, "Col3", DataType.DateTime) tab1.Columns.Add(col3) ' Create the ftable tab1.Create() ' Define Index object on the table by supplying the Table1 as the parent table and the primary key name in the constructor. Dim pk As New Index(tab1, "Table1_PK") pk.IndexKeyType = IndexKeyType.DriPrimaryKey ' Add Col1 as the Index Column Dim idxCol1 As New IndexedColumn(pk, "Col1") pk.IndexedColumns.Add(idxCol1) ' Create the Primary Key pk.Create() ' Create Unique Index on the table Dim unique As New Index(tab1, "Table1_Unique") unique.IndexKeyType = IndexKeyType.DriUniqueKey ' Add Col1 as the Unique Index Column Dim idxCol2 As New IndexedColumn(unique, "Col2") unique.IndexedColumns.Add(idxCol2) ' Create the Unique Index unique.Create() ' Create Table2 Dim tab2 As New Table(db, "Table2") Dim col21 As New Column(tab2, "Col21", DataType.NChar(20)) tab2.Columns.Add(col21) Dim col22 As New Column(tab2, "Col22", DataType.Int) tab2.Columns.Add(col22) tab2.Create() ' Define a Foreign Key object variable by supplying the Table2 as the parent table and the foreign key name in the constructor. Dim fk As New ForeignKey(tab2, "Table2_FK") ' Add Col22 as the foreign key column. Dim fkc As New ForeignKeyColumn(fk, "Col22", "Col1") fk.Columns.Add(fkc) fk.ReferencedTable = "Table1" ' Create the foreign key on the instance of SQL Server. fk.Create() ' Get list of Foreign Keys on Table2 For Each f As ForeignKey In tab2.ForeignKeys Console.WriteLine((f.Name + " " + f.ReferencedTable & " ") + f.ReferencedKey) Next ' Get list of Foreign Keys referencing table1 For Each tab As Table In db.Tables If (tab.Name.Equals(tab1.Name)) Then Continue For End If For Each f As ForeignKey In tab.ForeignKeys If f.ReferencedTable.Equals(tab1.Name) Then Console.WriteLine(f.Name + " " + f.Parent.Name) End If Next Next End Sub End Class

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

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

INSERT INTO [Level] (name) VALUES ('Beginner'), ('Elementary'), ('Pre-Intermediate'), ('Intermediate'), ('Upper-Intermediate'), ('Advanced'), ('Proficient')

Отслеживать
задан 5 окт 2018 в 19:19
user299321 user299321

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

5 окт 2018 в 19:21
Понял, спасибо) а можно как-то заполнять так чтобы чтобы прямо вставлять с одной таблицы в другую?
– user299321
5 окт 2018 в 19:23

Можно, но зависит от того какой именно SQL у вас, в MySQL это выглядит как Insert into table select from table2 , этот запрос скопирует содержимое table2 в table

Create Foreign Keys with cascade delete SQL Server

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

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

Создание внешнего ключа с каскадным удалением — использование оператора CREATE TABLE

Синтаксис

Синтаксис создания внешнего ключа с каскадным удалением с использованием оператора CREATE TABLE в SQL Server (Transact-SQL):

CREATE TABLE child_table
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.

CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, . child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, . parent_col_n)
ON DELETE CASCADE
[ ON UPDATE < NO ACTION | CASCADE | SET NULL | SET DEFAULT >]
);

child_table — имя дочерней таблицы, которую вы хотите создать.
column1 , column2 — столбцы, которые вы хотите создать в таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение остается пустым, база данных принимает значение NULL как значение по умолчанию.
fk_name — имя ограничения внешнего ключа, которое вы хотите создать.
child_col1 , child_col2 , . child_col_n — столбцы в child_table , которые будут ссылаться на первичный ключ в parent_table (родительской таблице).
parent_table — имя родительской таблицы, первичный ключ которой будет использоваться в child_table .
parent_col1 , parent_col2 , . parent_col3 — столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1 , child_col2 , . child_col_n в child_table .
ON DELETE CASCADE — указывает, что дочерние данные удаляются при удалении родительских данных.
ON UPDATE — необязательный. Он указывает, что делать с дочерними данными при обновлении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
NO ACTION — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что никакие действия не выполняются с дочерними данными при удалении или обновлении родительских данных.
CASCADE — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные либо удаляются, либо обновляются, когда родительские данные удаляются или обновляются.
SET NULL — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные установлены в NULL, когда родительские данные удаляются или обновляются.
SET DEFAULT — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.

Пример

Давайте рассмотрим пример создания внешнего ключа с каскадным удалением в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE.
Например: