Как связать таблицы в sql server management studio

7

SQL Server 2016: Create a Relationship

You can create a relationship between tables by using the GUI or SQL script. Here, I demonstrate both methods.

In relational database design, a is where two or more tables are linked together because they contain related data. This enables users to run queries for related data across multiple tables.

Here, we will create the following relationships.

The Method

Here’s how we’ll do it:

• We’ll use SQL to create the Albums table and one relationship.
• We’ll use the GUI to create the other relationship.

That way, you’ll get to see both methods of creating a relationship.

We only need to create one table because we’ve already created two of these tables previously in this tutorial (the Artists table via the GUI and the Genres table using SQL).

Create a Relationship using SQL

Open a new query window in SSMS and run the following code:

The first part of that statement creates the table.

The last part defines the relationship. This part:

The first two lines create the relationship. They create a foreign key constraint between the Albums.ArtistId column and the Artists.ArtistId column.

The last two lines specify what SQL Server should do if someone tries to delete or update a parent record that is being referenced by a record in the child table. In this case, NO ACTION means that the delete/update won’t go ahead. The user will just get an error.

You could change this to ON DELETE CASCADE if you want to be able to delete the parent and the child in one go (i.e. the delete will cascade from the parent to the child). The same logic applies to updates, by using ON UPDATE CASADE .

NO ACTION is the default value, so we could’ve done without those last two lines of code. However, I included it, because it’s an important factor to think about when creating foreign key constraints.

What’s a Foreign Key Constraint?

A defines a relationship between this table and another table. When you create a foreign key constraint, you create it against a specific column in the child table, to reference a specific column in parent table.

This makes the column in the child table a . The constraint ensures that any value that goes into this (foreign key) column corresponds with a value in the primary key column of the parent table. If someone tries to enter a value that doesn’t correspond with a value in the parent table’s primary key column, SQL Server will throw an error.

This helps enforce referential integrity. It prevents us from having orphaned records (child records that have no parent). Or in our example, albums that aren’t associated with any artist.

Create a Relationship via the GUI

Now we’ll create the other relationship via the SQL Server Mangement Studio’s GUI.

It would’ve been easier to include this in the above script but I wanted to demonstrate both methods of creating a relationship.

Open the Child Table in the Table Designer

Right-click on the child table (our newly created Albums table) and select Design from the contextual menu.

If you can’t see your newly created table in the Object Browser, you probably need to refresh the Object Browser.

Right-click on the Tables node and select Refresh .

Open the Foreign Key Relationships Dialog

Select Table Designer > Relationships. from the top menu.

Add the Relationship

The Foreign Key Relationships dialog will show you any existing relationships for the table. We can see the relationship that we established just before, when we created the table.

Click Add to add another relationship.

Select Tables And Columns Specification

A new relationship appears above the other one in the Selected Relationship list with a name of FK_Albums_Albums .

Ensuring that the the new relationship is selected, click Tables And Columns Specification in the right pane. An ellipses appears to the right of the property.

Click the ellipses ( . ) to launch the Tables and Columns dialog box.

The Tables and Columns Dialog Box

Here, you select the primary key table on the left pane, and the foreign key table on the right.

• Under Primary key table: select Genres as the table and GenreId as the column.
• Under Foreign key table: select Albums as the table and GenreId as the column.

SQL Server will suggest a name for the relationship. You can edit this if you wish. Otherwise, leave it as it is.

The Relationship

Your relationship will now be displayed correctly in the Foreign Key Relationships dialog box.

Saving The Relationship

Your relationship won’t be saved until you save the table. When you save the table, you will probably get a warning that two tables will be saved. This is to be expected, as the relationship affects two tables.

Click Yes to save both tables.

If you select Table Designer > Relationships. for the parent table, you’ll also see the relationship there.

Создание связи (отношения) типа «один ко многим» между таблицами базы данных Microsoft SQL Server

Создание связи (отношения) типа «один ко многим» между таблицами базы данных Microsoft SQL Server

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

;;.

Содержание

Поиск на других ресурсах:

Условие задачи

Дана база данных Microsoft SQL Server . База данных размещается в файлах «MyDatabase.mdf» и «MyDatabase.ldf» . Загрузить архив с готовыми для работы файлами базы данных можно здесь.

В базе данных заданы две таблицы с именами Source и Emission. Таблица Source определяет источник загрязненных выбросов. Таблица Emission определяет время выбросов и число загрязненных выбросов, которое было сформировано источником.

Структура таблиц следующая.

Используя средства системы Microsoft Visual Studio необходимо реализовать связь (отношение) «один ко многим» между таблицами Source и Emission по полю ID_Source.

Выполнение

1. Запуск Microsoft Visual Studio

Запустить систему визуальной разработки приложений Microsoft Visual Studio .

2. Создание/подключение базы данных

На этом шаге нужно подключить (или создать) готовую базу данных «MyDataBase.mdf» , которая состоит из двух файлов:

• файл «MyDataBase.mdf» ;
• файл «MyDataBase.ldf» .

После подключения окно Server Explorer будет иметь вид, как показано на рисунке 1.

Рис. 1. Окно Server Explorer после подключения базы данных «MyDataBase.mdf»

3. Поля ID_Source и ID_Emission

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

Поле ID_Source таблицы Source есть первичным ключом.

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

4. Установление связи между таблицами

4.1. Снятие опции «Prevent saving changes that require table re-creation»

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

Для этого нужно выполнить следующую последовательность шагов:

• перейти в меню Tools главного меню MS Visual Studio ;
• в меню Tools выбрать команду «Options…» . В результате откроется окно «Options» (рисунок 2);
• в левой части окна «Options» последовательно раскрыть вкладки «Database Tools» -> «Table and Database Designers» (рисунок 2);
• выбрать элемент «Table and Diagram Options» . В результате в правой части окна активируется группа элементов «Table Options» ;
• в группе «Table Options» снять пометку из опции «Prevent saving changes that require table re-creation» (рисунок 2) и подтвердить выбор (кнопка OK ).

После выполненных действий можно создавать связь между таблицами.

Рис. 2. Опция «Prevent saving changes that require table re-creation»

4.2. Установление первичного ключа ( Primary Key ) в таблице Source

Как видно из структуры таблиц (рисунок 1) общим для таблиц есть поле ID_Source. Связь между таблицами будет осуществляться по этому полю.

В таблице Source нужно установить поле ID_Source как «Первичный ключ» ( Primary Key ).

Чтобы установить первичный ключ нужно выполнить такие действия:

• перейти в режим редактирования таблицы Source выбором команды «Open Table Definition» (рисунок 3). Откроется окно редактирования таблицы;
• сделать клик правой кнопкой «мышки» на строке ID_Source и в контекстном меню выбрать команду «Set Primary Key» . В результате поле ID_Source будет обозначено как поле, которое есть первичным ключом (рисунок 5);
• сохранить и закрыть таблицу Source .

Рис. 3. Команда «Open Table Definition»

Рис. 4. Установление первичного ключа в таблице Source

Рис. 5. Поле ID_Source в таблице Source после установления первичного ключа

В таблице Emission не обязательно устанавливать первичный ключ.

4.3. Создание связи между таблицами по полю ID_Source

Для создания связей между таблицами используется элемент “Database Diagrams» базы данных «MyDataBase.mdf» . Чтобы создать связь между таблицами нужно выполнить следующие действия:

• с помощью клика правой кнопкой «мышки» вызвать контекстное меню (рисунок 6). В меню выбрать команду «Add New Diagram» . В результате, база данных создаст пустую диаграмму. Будет выведено окно «Add Table» добавления таблиц в диаграмму (рисунок 7);
• поочередно выбрать нужные таблицы (Source, Emission) и подтвердить выбор нажатием на кнопке «Add Table» ;
• закрыть окно «Add Table» .

Рис. 6. Команда добавления новой диаграммы

Рис. 7. Окно «Add Table» добавления таблиц в диаграмму

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

Рис. 8. Таблицы Source и Emission

Чтобы начать устанавливать отношение между таблицами, нужно сделать клик на поле ID_Source таблицы Source, а потом (не отпуская кнопку мышки) перетянуть его на поле Source таблицы Emission .

В результате последовательно откроются два окна: Tables and Columns (рисунок 9) и Foreign Key Relationship (рисунок 10), в которых нужно оставить все как есть и подтвердить свой выбор на кнопке OK .

В окне «Tables and Columns» есть такие поля (рисунок 9):

• поле «Relationship name» . В этом поле задается имя объекта, который символизирует соединение (отношение) между таблицами. С помощью этого объекта (имени) можно управлять некоторыми свойствами связи (отношения). В нашем случае соединение (отношение) имеет название FK_Emission_Source ;
• поле «Primary key table:» . Это поле задает таблицу, которая есть первичной по отношению к другой таблице. В нашем случае первичной есть таблица Source. Поле, которое служит первичным ключом таблицы имеет название ID_Source ;
• поле «Foreign key table:» указывает название таблицы ( Emission ) и поля в этой таблице ( ID_Source ), которое есть внешним ключом.

Рис. 9. Окно настройки параметров связи (отношения) между таблицами

В окне «Foreign Key Relationship» настраиваются свойства соединения. Можно оставить все как есть.

Рис. 10. Настройка свойств соединения FK_Emission_Source

4.4. Диаграмма связи

После создания связи окно диаграммы будет иметь вид, как показано на рисунке 11.

Рис. 11. Вид диаграммы после установки отношения (связи) между таблицами Source и Emission

Как видно из рисунка, конец соединения (отношения), что примыкает к таблице Source имеет вид ключа. А конец соединения, которое примыкает к таблице Emission имеет вид знака бесконечность ∞ .

Это означает, что в таблице Source числовое значение в поле ID_Source может встречаться только один раз. А в таблице Emission числовое значение ID_Source может повторяться (бесконечное количество раз). Таким образом можно представить любое множество уникальных объектов, которые имеют свойство повторяться в некоторой предметной области.

После закрытия диаграммы ее нужно сохранить под некоторым именем, например Diagram1 (рисунок 12). Система выдаст соответствующее окно уточнения.

Рис. 12. Задание имени для диаграммы

Также, система может вывести окно сохранения таблиц в базе данных (рисунок 13), поскольку между таблицами уже существует отношение (связь). В этом окне нужно указать «Yes» .

Рис. 13. Окно сохранения таблиц в базе данных в связи с изменениями

После выполненных действий, диаграмма Diagram1 отобразится в окне Server Explorer (рис. 14). С помощью команд контекстного меню есть возможность управлять диаграммой. Так, например, команда «Design Database Diagram» переводит диаграмму в режим редактирования, в котором можно изменять связи между таблицами базы данных.

Рис. 14. Команда редактирования связей (отношений) между таблицами

5. Программное управление данными

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

Foreign Keys in SQL Server:

Here you will learn what is a foreign key and how to established a relationship between two tables using a foreign key in the SQL Server database.

What is Foreign Key?

The foreign key establishes the relationship between the two tables and enforces referential integrity in the SQL Server. For example, the following Employee table has a foreign key column DepartmentID that links to a primary key column of the Department table.

• A foreign key column can be linked to a primary key or a unique key column of the same or another table.
• The table having the foreign key constraint is called the child table, and the table being referenced by the foreign key is called the parent table. E.g. Employee is a child table and Department is a parent table.
• A value other than NULL is entered in the column of the foreign key constraint, that value must already exist in the referenced column of the parent table. Else you will get a foreign key violation error.
• Foreign key constraints can reference tables within the same database in the same server.
• Foreign key constraints can be defined to reference another column in the same table. This is referred to as a self-reference.
• A foreign key constraint on a single column (Column level constraint) can reference only one column in the parent table and should have the same data type as the referenced column.
• A foreign key constraint defined at the table level (on a combination of columns) should have the same number of reference columns as the number of columns defined in the constraint list. The data type of each column in the constraint must be the same as the corresponding column in the column list.
• There is no limit on the number of foreign key constraints a table can contain that references other tables. However, it is limited by the hardware configuration and the database design.
• Foreign key constraints are not enforced on temporary tables.

Create Foreign Key Constraint in SQL Server

Foreign key constraints can be created in two ways in SQL Server:

• Using T-SQL
• Using SQL Server Management Studio

Create a Foreign Key using T-SQL

A foreign key can be configured in the create table T-SQL script. Append CONSTRAINT REFERENCES statement at the end of all column declaration.

In the above syntax, <foreignkey_name> is the name of a foreign key that should be in the FK_TableName_ReferenceTableName format to recognize it easily. This will give you an idea about the reference table. <reference_tablename> is the name of the table where the referred column is defined as primary key or unique key.

The following T-SQL script creates a new table Employee and configures a foreign key constraint FK_Employee_Department on the DepartmentID column, which references the DepartmentID primary key of the Department table.

ON DELETE CASCADE: When we create a foreign key using the delete cascade option, it deletes the referencing columns in the child table whenever the referenced row in the parent table with the primary key is deleted.

ON UPDATE CASCADE: When a foreign key is created with the update cascade option, the referencing rows in the child table are updated whenever the referenced row in the parent table with the primary key is updated.

Create a Foreign key in an Existing Table

Use the ALTET TABLE ADD CONSTRAINT statement to create a foreign key in an existing table.

The following query adds a new foreign key constraint FK_Employee_Department on the DepartmentID column.

Create a Foreign Key using SSMS

Here, we will configure the DepartmentID column as a foreign key in the Employee table that points to the DepartmentID PK column of the Department table using SQL Server Management Studio.

Open SSMS and expand the HR database. Right-click on the Employee table and click on the Design option, as shown below.

Create a Foreign Key in SQL Server

This will open the Employee table in the design mode.

Now, right-click anywhere on the table designer and select Relationships. as shown below.

Define Relationships

This will open the Foreign Key Relationships dialog box, as shown below.

Add Foreign Keys in SQL Server

Now, click on the Add button to configure a new foreign key, as shown below.

Configure a Foreign Key in SQL Server

Now, to configure the primary key and foreign key relationship, click on the Tables and Column Specification [. ] button. This will open Tables and Columns dialog box where you can select primary key and foreign key relationship.

Here, we are configuring the DepartmentID column in the Employee table as a foreign key, which points to the primary key column DepartmentID of the Department table. So, select primary table and key in the left side and foreign key table and column in the right side, as shown below.

Configure a Foreign Keys in SQL Server

The following defines a foreign key DepartmentID in the Employee table.

Configure a Foreign Key in SQL Server

Click OK to create the relationship and click on Close to close the dialog box.

Now, save your changes. This will create a one-to-many relationship between the Employee and Department table by setting a foreign key on the DepartmentID column in the Employee table, as shown below.

Foreign Key Relationship in SQL Server

SQL FOREIGN KEY: How to Create in SQL Server with Example

A Foreign Key provides a way of enforcing referential integrity within SQL Server. In simple words, foreign key ensures values in one table must be present in another table.

Rules for FOREIGN KEY

• NULL is allowed in SQL Foreign key.
• The table being referenced is called the Parent Table
• The table with the Foreign Key in SQL is called Child Table.
• The SQL Foreign Key in child table references the primary key in the parent table.
• This parent-child relationship enforces the rule which is known as “Referential Integrity.”

The Below Foreign Key in SQL example with diagram summarizes all the above points for FOREIGN KEY

In this tutorial, you will learn

How to Create FOREIGN KEY in SQL

We can Create a Foreign Key in SQL server in 2 ways:

1. SQL Server Management Studio
2. T-SQL

SQL Server Management Studio

Parent Table: Say, we have an existing Parent table as ‘Course.’ Course_ID and Course_name are two columns with Course_Id as Primary Key.

Child Table: We need to create the second table as a child table. ‘Course_ID’ and ‘Course_Strength’ as two columns. However, ‘Course_ID’ shall be Foreign Key.

Step 1) Right Click on Tables>New> Table…

Step 2) Enter two column name as ‘Course_ID’ and ‘Course_Strength.’ Right click on ‘Course_Id’ Column. Now click on Relationship.

Step 3) In ‘Foreign Key Relationship,’ Click ‘Add’

Step 4) In ‘Table and Column Spec’ click on ‘…’ icon

Step 5) Select ‘Primary Key Table’ as ‘COURSE’ and the new table now being created as ‘Foreign Key Table’ from the drop down.

Step 6) ‘Primary Key Table’ – Select ‘Course_Id’ column as ‘Primary Key table’ column.

‘Foreign Key Table’- Select ‘Course_Id’ column as ‘Foreign Key table’ column. Click OK.

Step 7) Click on Add.

Step 8) Give the Table name as ‘Course_Strength’ and click on OK.

Result: We have set Parent-child relationship between ‘Course’ and ‘Course_strength.’

T-SQL: Create a Parent-child table using T-SQL

Parent Table: Reconsider, we have an existing Parent table with table name as ‘Course.’

Course_ID and Course_name are two columns with Course_Id as Primary Key.

Child Table: We need to create the second table as the child table with the name as ‘Course_Strength_TSQL.’

‘Course_ID’ and ‘Course_Strength’ as two columns for child table Course_Strength_TSQL.’ However, ‘Course_ID’ shall be Foreign Key.

Below is the syntax to create a table with FOREIGN KEY

Syntax:

Here is a description of the above parameters:

• childTable is the name of the table that is to be created.
• column_1, column_2- the columns to be added to the table.
• fkey_name- the name of the foreign key constraint to be created.
• child_column1, child_column2…child_column_n- the name of chidTable columns to reference the primary key in parentTable.
• parentTable- the name of parent table whose key is to be referenced in the child table.
• parent_column1, parent_column2, … parent_column3- the columns making up the primary key of parent table.
• ON DELETE. An optional parameter. It specifies what happens to the child data after deletion of the parent data. Some of the values for this parameter include NO ACTION, SET NULL, CASCADE, or SET DEFAULT.
• ON UPDATE- An optional parameter. It specifies what happens to the child data after update on the parent data. Some of the values for this parameter include NO ACTION, SET NULL, CASCADE, or SET DEFAULT.
• NO ACTION- used together with ON DELETE and ON UPDATE. It means that nothing will happen to the child data after the update or deletion of the parent data.
• CASCADE- used together with ON DELETE and ON UPDATE. The child data will either be deleted or updated after the parent data has been deleted or updated.
• SET NULL- used together with ON DELETE and ON UPDATE. The child will be set to null after the parent data has been updated or deleted.
• SET DEFAULT- used together with ON DELETE and ON UPDATE. The child data will be set to default values after an update or delete on the parent data.

Let’s see a Foreign Key in SQL example to create a table with One Column as a FOREIGN KEY:

Foreign Key in SQL example

Query:

Step 1) Run the query by clicking on execute.

Result: We have set Parent-child relationship between ‘Course’ and ‘Course_strength_TSQL.’

Using ALTER TABLE

Now we will learn how to use Foreign Key in SQL and add Foreign Key in SQL server using the ALTER TABLE statement, we will use the syntax given below:

Here is a description of the parameters used above:

• childTable is the name of the table that is to be created.
• column_1, column_2- the columns to be added to the table.
• fkey_name- the name of the foreign key constraint to be created.
• child_column1, child_column2…child_column_n- the name of chidTable columns to reference the primary key in parentTable.
• parentTable- the name of parent table whose key is to be referenced in the child table.
• parent_column1, parent_column2, … parent_column3- the columns making up the primary key of parent table.

Alter table add Foreign Key example:

We have created a foreign key named fkey_student_admission on the department table. This foreign key references the admission column of the students table.

Example Query FOREIGN KEY

First, let’s see our Parent Table Data, COURSE.

Query:

Now let’s insert some row in Child table: ‘Course_strength_TSQL.’

We will try to insert two types of rows

1. The first type, for which Course_Id in child table will exist in Course_Id of Parent table. i.e. Course_Id = 1 and 2
2. The second type, for which Course_Id in child table doesn’t exist in the Course_Id of Parent table. i.e. Course_Id = 5

Query:

Result: Let’s run the Query together to See our Parent and Child table

Row with Course_ID 1 and 2 exist in Course_strength table. Whereas, Course_ID 5 is an exception.