Creating a “one-to-many” relationship between tables in a Microsoft SQL Server database

This topic shows how to create a relationship between tables on a certain field. This topic is based on the knowledge of previous topics:

An example of creating/connecting local MS SQL Server database located in the “*.mdf” file;
An example of creating the table in local MS SQL Server database located in the “*.mdf” file;
Creating an autoincrement field (counter) in the MS SQL Server database table, which is located in the “*.mdf” file.

Search other websites:

Task

The Microsoft SQL Server database is given. The database is located in the files “MyDatabase.mdf” and “MyDatabase.ldf”. You can download the archive with the ready-made database files here.

The database has two tables named Source and Emission. The Source table defines the source of polluted emissions. The Emission table defines the time of emissions and the number of polluted emissions that was generated by the source.

The structure of the tables is as follows.

Table Source

Field name	Data type	Comment
ID_Source	int	Key field, unique field (counter), primary key
Name	char[50]	Name, character string
Address	char[100]	Address, character string

Table Emission

Field name	Data type	Comment
ID_Emission	int	Key field, unique field (counter)
ID_Source	int	Foreign key, the value Source.ID_Source
count	float	Number of emissions
Text	char[100]	Comment
date	datetime	Emission date and time

Using the tools of the Microsoft Visual Studio system, you need to implement a one-to-many relationship between the Source and Emission tables for the ID_Source field.

⇑

Instructions

1. Run Microsoft Visual Studio

Run Microsoft Visual Studio.

2. Creating/connecting database

At this step, you need to connect (or create) the finished database “MyDataBase.mdf”, which consists of two files:

“MyDataBase.mdf”;
“MyDataBase.ldf”.

Archive with files can be downloaded here.

After connecting the Server Explorer window will look as shown in Figure 1.

Figure 1. The Server Explorer window after connecting the database “MyDataBase.mdf”

⇑

3. Fields ID_Source and ID_Emission

It should be noted that the ID_Source and ID_Emission fields are unique counters. Such fields are used in databases to ensure the uniqueness of each record of the table.

The ID_Source field of the Source table is the primary key.

A detailed example of creating a unique field that is a counter is described in the article:

Creating an autoincrement field (counter) in the MS SQL Server database, which is located in the “*.mdf”-file.

⇑

4. Setting a relation between the tables

4.1. Removing the option “Prevent saving changes that require table re-creation”

By default, MS Visual Studio system prevents saving changes that require re-create the tables.

To allow changes to the tables, you need to configure (deselect) the option

Prevent saving changes that require table re-creation

To do this, perform the following sequence of steps:

go to the Tools menu in the main menu of MS Visual Studio;
in the Tools menu select the command “Options …”. This will open the “Options” window (see Figure 2);
In the left part of the “Options” window, expand the “Database Tools” -> “Table and Database Designers” tabs (see Figure 2);
Select “Table and Diagram Options”. As a result, the “Table Options” group is activated in the right part of the window;
in the “Table Options” group, uncheck the option “Prevent saving changes that require table re-creation” (Figure 2) and confirm the selection (OK button).

After completing the steps, you can create a relationship between the tables.

Figure 2. The “Prevent saving changes that require table re-creation” option

⇑

4.2. Setting the Primary Key in the Source table

As you can see from the tables structure (Figure 1), the ID_Source field is common to both tables. The connection between the tables will be carried out by this field.

In the Source table, you need to set the ID_Source field as the “Primary Key”.

To set a primary key, you need to do the following:

switch to the editing mode of the Source table by selecting the “Open Table Definition” command (Figure 3). The window for editing the table opens;
Right-click on the mouse on the ID_Source line and select “Set Primary Key” from the context menu. As a result, the ID_Source field will be designated as a field, which is the primary key (Figure 5);
save and close the Source table.

Figure 3. The command “Open Table Definition”

Figure 4. Setting a primary key in the Source table

Figure 5. The ID_Source field in the Source table after setting the primary key

In the Emission table, it is not necessary to set the primary key.

⇑

4.3. Creating relationships between the tables by ID_Source field

To create relationships between tables, you need to use the element “Database Diagrams” database “MyDataBase.mdf”. To create a relationship between tables, you need to do the following:

with the help of a right click on the “mouse” call the context menu (Figure 6). Select “Add New Diagram” from the menu. As a result, the database will create an empty diagram. The “Add Table” window will be added to add the tables to the diagram (Figure 7);
alternately select the necessary tables (Source, Emission) and confirm the selection by clicking the “Add Table” button;
close the “Add Table” window.

Figure 6. The command to add a new diagram

Figure 7. The ‘Add Table’ window to add tables to the diagram

After the performed actions, the diagram window will have the form, as shown in Figure 8. So far, the tables have no connection with each other.

Figure 8. Tables Source and Emission

To start the relationship between the tables, you need to click on the ID_Source field of the Source table, and then (without releasing the mouse button) drag it to the Source field of the Emission table.

As a result, two windows will open in sequence: ‘Tables and Columns’ (Figure 9) and ‘Foreign Key Relationship’ (Figure 10), in which you need to leave everything as is and confirm your choice with the OK button.

In the “Tables and Columns” window there are such fields (Figure 9):

the field “Relationship name”. This field specifies the name of the object, which symbolizes the connection (relationship) between the tables. With the help of this object (name), you can control some properties of the relationship. In our case, the connection (relation) is called FK_Emission_Source;
field “Primary key table”. This field specifies the table that is primary relative to the other table. In our case, the ‘Source’ table is the primary table. The field that serves as the primary key of the table is called ID_Source;
the “Foreign key table:” field specifies the name of the table (Emission) and the field in this table (ID_Source), which is the foreign key.

Figure 9. Window for setting relation parameters between tables

In the “Foreign Key Relationship” window, the connection properties are configured. You can leave it as it is.

Figure 10. Configure connection properties FK_Emission_Source

⇑

4.4. Communication diagram

After creating the relationship, the diagram window will have the form shown in Figure 11.

Figure 11. The diagram view after setting the relationship between the Source and Emission tables

As you can see from the figure, the end of the connection (relationship) that is adjacent to the Source table has the form of a key. And the end of the connection that adjoins the Emission table has the sign of infinity.

This means that in the Source table the numeric value in the ID_Source field can occur only once. And in the Emission table, the numerical value of ID_Source can be repeated (an infinite number of times). Thus, you can imagine any set of unique objects that have the property of being repeated in some subject area.

After the diagram is closed, it should be saved under a certain name, for example Diagram1 (Figure 12). The system will display the corresponding refinement window.

Figure 12. Set a name for the diagram

Also, the system can display a table saving window in the database (Figure 13), because there already exists a relation between the tables. In this window, you need to specify “Yes”.

Figure 13. The window for saving tables in the database when changed

After the performed actions Diagram1 diagram will be displayed in the Server Explorer window (Fig. 14). Using the commands of the context menu, you can control the diagram. For example, the “Design Database Diagram” command takes the diagram to edit mode, where you can change the relationships between the database tables.

Figure 14. The command for editing relationships between tables

⇑

5. The data management

After creating a relationship between tables, you can create a project that will manage the data in the tables. But this is a completely different topic.

BestProg

Creating a “one-to-many” relationship between tables in a Microsoft SQL Server database