Create a Relationship in SQL

Posted on by Ian

In SQL, you create a relationship by creating a foreign key constraint.

More specifically, you have a parent table and a child table. The parent contains the primary key, and the child table contains a foreign key that references the primary key of the parent table.

When you use SQL to create a relationship, you can create the relationship at the time you create the table, or you can create it later (by altering the table). This article covers both scenarios.

Create a Relationship When Creating the Table

Here’s an example of creating a relationship within your CREATE TABLE statement at the time you create the table.

CREATE TABLE Parent (
  ParentId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ParentName nvarchar(255) NOT NULL
)
CREATE TABLE Child (
  ChildId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ChildName nvarchar(255) NOT NULL,
  ParentId int NOT NULL
  CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId)     
    REFERENCES Parent (ParentId)
);

Here I created two tables; one called Parent and the other called Child.

I created the relationship within the table definition for child. The relationship is created with the CONSTRAINT argument. Note that this is still inside the CREATE TABLE statement.

The relationship needs a name. In this case I called it FK_Child_Parent. The FOREIGN KEY part is followed by the name of the column (in the child table) that will be the foreign key.

The REFERENCES part specifies the column that the foreign key will reference. In this case it references the ParentId column of the Parent table. This is done using REFERENCES Parent (ParentId).

That’s all that’s required to create the relationship.

Note that the examples on this page were done using SQL Server. Depending on your DBMS, you may need to change some details of the column definitions.

For example IDENTITY is SQL Server’s version of what is sometimes called AUTO_INCREMENT in other DBMSs (such as MySQL). If you use SQLite, then here’s how to create an auto-incrementing column in SQLite.

Add a Relationship to an Existing Table

You can also add a relationship to an existing table, simply by using the ALTER TABLE statement.

Let’s pretend that we didn’t create the relationship when creating the two tables from the previous example. So let’s pretend that we’d done this instead:

CREATE TABLE Parent (
  ParentId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ParentName nvarchar(255) NOT NULL
)
CREATE TABLE Child (
  ChildId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ChildName nvarchar(255) NOT NULL,
  ParentId int NOT NULL
);

So in this scenario, all we did was create two tables. No relationship was created between them.

Now, after creating the tables, we suddenly remember “oh dang, I forgot to create a relationship!”.

No problem, we can now do this:

ALTER TABLE Child
  ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId)     
    REFERENCES Parent (ParentId);

Done. We’ve just added the relationship using the same details as per the previous example.

Note that SQLite doesn’t support adding foreign keys with the ALTER TABLE statement. See how to add a foreign key to an existing table in SQLite for more about that.

On Update/Delete

By default, SQL Server relationships are created using ON DELETE NO ACTION and ON UPDATE NO ACTION. Therefore, the previous examples were created using this setting.

However, different DBMSs may use other default settings.

Either way, you can explicitly specify this in your code. So we can modify the previous example to look like this:

ALTER TABLE Child
  ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId)     
    REFERENCES Parent (ParentId)
    ON DELETE NO ACTION    
    ON UPDATE NO ACTION;

What this actually means is that, if someone was to try to delete or update a record in the primary key, an error would occur and the change would be rolled back. This is SQL Server’s way of preventing any changes that could break the referential integrity of your system.

Basically, the reason you create a relationship in the first place is to enforce referential integrity.

However, you do have some options with how you want SQL Server to deal with these situations.

Specifically, you can use any of the following values:

  • NO ACTION: An error is raised and the delete/update action on the row in the parent table is rolled back.
  • CASCADE: Corresponding rows are deleted from/updated in the referencing table if that row is deleted from/updated in the parent table.
  • SET NULL: All the values that make up the foreign key are set to NULL if the corresponding row in the parent table is deleted or updated. This requires that the foreign key columns are nullable.
  • SET DEFAULT: All the values that make up the foreign key are set to their default values if the corresponding row in the parent table is deleted or updated. For this constraint to execute, all foreign key columns must have default definitions. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column.