1 Introduction
2 The assumed Situation
3 Setting up the database and the required tables
- 3.1 Primary Key
- 3.2 Foreign Key
4 Connecting the two tables
5 Filling the tables
6 Shorter method
7 Conclusion

1 Introduction

After I showed in my last post how to create a SQL database and how to insert tables with values into it, let’s go one step further.

As we know from the ERD post, the tables in a database are always related to each other with some relationship.

In this post I would like to point out a circumstance where it is a 1:1 relationship.

2 The assumed Situation

Let’s say we are asked to design a database for a registry office. We have information about the groom and about the bride. This information should be stored in a husband and a wife table which are in a 1:1 relationship to each other. This means that each groom is assigned exactly one bride and vice versa. I use the Modified Chen Notation (MC Notation) below.

3 Setting up the database and the required tables

Since I would like to have the output of SSMS always in English I use the following syntax.

SET LANGUAGE ENGLISH

How I create the database and the tables can be read in my post Create a Database.

CREATE DATABASE Registry_Office;


USE Registry_Office;

CREATE TABLE Husband
    (Husband_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL)
    ;

CREATE TABLE Wife
    (Wife_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL)
    ;

Here is the result:

3.1 Primary Key

Short and sweet:

A primary key is used to ensure that the data in a particular column is unique. It is a column that must not contain null values. Either this column is already provided in the table or it is specially created by the database according to a defined sequence.

As you have probably already noticed, I had the columns Husband_ID and Wife_ID stored as Primary Key when I created the two tables. The relationship should be as follows:

One can view the relationships of the tables stored in a database in SSMS. To do this, right-click on ‘Views’ in the Object Explorer and select ‘New View’. Afterwards you will be asked which tables should be displayed. Select your desired tables and click on OK.

What we see now is the following output:

Here, there is still no connection between the two tables … Why is that? The answer has to do with the Foreign Key.

3.2 Foreign Key

A foreign key is a column or a group of columns in a relational database table that creates a link between data in two tables. It is a column that refers to a column (usually the primary key) of another table.

We have not yet assigned this foreign key. So we do this now as the next step.

4 Connecting the two tables

Since this is a 1:1 relationship, only one foreign key is needed. It does not matter in which table it is stored. We add it to the ‘Husband’ table in this example.

ALTER TABLE Husband ADD Wife_ID INT NOT NULL FOREIGN KEY REFERENCES Wife(Wife_ID);

The underlying ERD now looks like this:

Let’s take another look at Views (via SSMS).

Now we can also see in SSMS that and what relationship exists between the two tables.

If you want to see a representation via Oracle Data Modeler, check out this chapter of my post Oracle SQL Developer Data Modeler: ERDs from old Posts with Oracle Data Modeler

5 Filling the tables

To see how we can use this relationship we first fill the two tables with values.

Important!

First, the table Wife must be filled, as the stored Foreign Key (‘Wife_ID’) in the table Husband refers to this table (Wife).

Filling the Wife-Table:

INSERT INTO Wife (Wife_ID, First_Name, Last_Name) VALUES (1, 'Klara', 'Brown')
INSERT INTO Wife (Wife_ID, First_Name, Last_Name) VALUES (2, 'Jessy', 'Williams')
INSERT INTO Wife (Wife_ID, First_Name, Last_Name) VALUES (3, 'Emily', 'Taylor')

Filling the Husband-Table:

INSERT INTO Husband (Husband_ID, First_Name, Last_Name, Wife_ID) VALUES (1, 'Max', 'Steel', 1)
INSERT INTO Husband (Husband_ID, First_Name, Last_Name, Wife_ID) VALUES (2, 'Alex', 'Smith', 2)
INSERT INTO Husband (Husband_ID, First_Name, Last_Name, Wife_ID) VALUES (3, 'Marc', 'Pike', 3)

Have a look at the two filled tables:

SELECT * FROM Husband
SELECT * FROM Wife

Now we perform a simple merge of the two tables using the Primary Key and Foreign Key.

SELECT * 
    FROM Husband JOIN Wife 
    ON Husband.Wife_ID = Wife.Wife_ID;

At this point, it should be mentioned that this is a rather simple combination that should exemplify the usefulness of primary and foreign keys. I will report on the topic of how to execute joins correctly and sensibly in a later post.

6 Shorter method

In the previous example, I first created two tables and added the Foreign Key afterwards. You can also add it right when you create the tables. I will show you how to do this in the following example:

CREATE DATABASE Registry_Office2;

USE Registry_Office2;


CREATE TABLE Wife
    (Wife_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL)
    ;


CREATE TABLE Husband
    (Husband_ID INT NOT NULL PRIMARY KEY,
    First_Name VARCHAR(100) NOT NULL,
    Last_Name VARCHAR(100) NOT NULL,
    Wife_ID INT NOT NULL FOREIGN KEY REFERENCES Wife(Wife_ID))
    ;


SELECT * FROM Husband
SELECT * FROM Wife

It is only important to create the wife table first, otherwise the error will be thrown that the column ‘Wife_ID’ from the table ‘Wife’ does not exist.

7 Conclusion

In this post I have shown a simple example of a 1:1 relationship of two tables and how to create them in a database respectively how to build the relationship to them among themselves accordingly.

In addition, I have shown how to output relationships of tables within a database from SSMS.

In subsequent posts, I will discuss other forms of relationships.

Create a 1:1 Relationship