How to Create Foreign Key Constraints in SQL

If you own an enterprise, you must have encountered the value and need for data in your business. Having the means to store and manipulate databases adds more value to the business.

Databases are organized in a particular convention and allow you to structure data in connections, bringing us to relational databases, which have been embraced as a form of data management since the 1970s. And in today’s market, relational databases are preferred for their capabilities when manipulating data.

While there are many available relational databases out there, MySQL has made it to the leading, ranking as the world wide’s number two, according to Statista, as of January 2022.

In the SQL server, constraints are predefined rules and limitations enforced in either a single or multiple columns; they are tied to the values in the column and aid in maintaining the integrity, accuracy, and reliability of the specified columns’ data.

Simply put, only data that meets the constraint rule is successfully inserted into the column. The insert operation is terminated if the data does not meet the criteria.

This post assumes you have encountered relational databases, specifically – MySQL, and looking forward to strengthening your knowledge in the domain. Ultimately, I’ll share some tips for interacting with Foreign key constraints.

Primary Key Constraints – A Recap

A table in SQL entails a column or several containing key values that precisely pinpoint each row in the systems. The column or columns entitled primary key (PK) of the table has the role of enforcing entity integrity of the table. Primary key constraints guarantee unique data and are often defined on an identity column.

Upon specifying the primary key constraints for your table, the database engine automatically imposes data uniqueness by generating unique indexes for each of the primary columns. Primary keys offer an extreme advantage when used in queries by providing fast data access.

If primary key constraints are defined on multiple columns, it is referred to as a composite or compound primary key. And in this case, each primary key column can contain duplicate values. However, the combined values from all the columns in the primary key must be unique.

A good example is a case where you have a table with columns `id`, `names`, and `age`. When you define its primary key constraint on the combination of `id` and `names`, you can have duplicate instances of either `id` or `names` values. Still, each combination must be unique to avoid duplicate rows. So, you could have records with `id=1` and `name=Walter`, and `age-22 “and `id=1`, `name=Henry` and `age=27`, but you cannot have other records with `id=1` and `name=Walter` because the combination is not unique.

Here are some essential aspects to know:

1. A table contains only one primary key constraint.
2. Primary keys cannot exceed 16 columns and a maximum length of 900 characters.
3. The indexes generated by primary keys can increase those in the table. However, the number of clustered indexes on a table cannot exceed 1, and the number of non-clustered indexes on a table is limited to 999.
4. When clustered and non-clustered are unspecified for a key constraint, clustered is taken automatically.
5. All columns declared within a primary key constraint should be defined as not null. If this is not the case, all columns linked in the constraint have their nullability robotically set to not null.
6. When primary keys are defined on common language runtime (CLR) user-defined column type, the type implementation must support binary ordering.

Foreign Key Constraints – A Rundown

A foreign key (FK) entails a column or a combination of several used to create and bind a link between two tables and manages the data to be stored in a foreign key table.

A foreign key reference entails creating a link between two tables; when a column or columns holding the primary key for another table get referenced by a column or columns in a different table.

In the foreign key reference scenario, a connection is created between two tables when a column or columns holding primary keys in a table are referenced by columns in another.

In a practical use case, you can have a table, Sales.SalesOrderHeader, with a foreign key linking to another table, Sales. Person, because there is a logical relationship between salespeople and sales orders.

Here, the SalesPersonID in the column SalesOrderHeader blends with the primary key column of the SalesPerson table. The SalesPerson table foreign key is the SalesPersonID column in the SalesOrderHeader.

This relationship defines a rule: a SalesPersonID value cannot be in your SalesOrderHeader table if it’s nonexistent in the SalesPerson table.

A table can reference up to 253 other columns and tables as foreign keys, alternatively called outgoing references. Since 2016, the SQL server has raised the number of tables and columns you can reference in a single table, also known as incoming references, from 253 to 10000. However, the increase comes with some restrictions:

1. Foreign key references exceeding 253 are only available for the DELETE DML operations. MERGE and UPDATE are not supported.
2. Tables with foreign key references to themselves are to a max of 253 foreign key references.
3. For column-store indexes, memory-optimized tables, and partitioned foreign key tables, foreign key references are limited to 253.

What Are the Benefits of Foreign Keys?

As prementioned before, foreign key constraints play an essential role in safeguarding the integrity and data consistency in the relational database. Here is a breakdown of reasons why foreign key constraints are essential.

1. Referential Integrity – Foreign key constraints guarantee that every child table record corresponds to a primary table record, ensuring data consistency across both tables.
2. Preventing orphaned records – If you delete a parent table, foreign key constraints ensure that your associated child table is also deleted, preventing instances of orphaned records that could lead to data inconsistency.
3. Improved performance – Foreign key constraints boost query performance by allowing the database management system to optimize queries based on table relationships.

Indexes of Foreign Key Constraints

Foreign key constraints do not automatically create corresponding indexes like the primary. You can manually create indexes for foreign key constraints; it’s beneficial for the following reasons.

• Foreign key columns are often used in the join criteria when combining data from related tables in queries by matching the columns tied to the constraint. Indexes aid the database in finding associated data in a foreign table.
• If you change primary key constraints, they are checked with the foreign ones in related tables.

It’s not compulsory to create indexes. You can still combine data from two tables without specifying the primary and foreign key constraints. However, adding foreign key constraints optimizes the tables and combines them in a query that meets its criteria of using the keys. If you change primary key constraints, they are checked with the foreign ones in related.

Tips to Create Foreign Key Constraints in SQL

You have already spent significant time on the speculation; answered the why. Let’s shift our focus and narrow it down to the tactics for creating foreign key constraints; answer the how.

A `Foreign Key` field in a table refers to the `Primary Key` of another. The table with the primary key is your parent table. And the table with the foreign key is called the child table. Let’s dive in.

Creating a Foreign Key While Creating a Table

When creating a table, you can also create a foreign key constraint to uphold referential integrity. Here’s how to do it:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The code above creates a table called ‘orders’ with the primary integer key ‘order_id’, another integer ‘customer_id’, and the date ‘order_date’. In this case, the FOREIGN KEY constraint is added to the ‘customer_id’ column and references the ‘customer_id’ in your ‘customers’ table.

Creating a Foreign Key After Creating a Table

Suppose you have already created a table and want to add a foreign key constraint; use the `ALTER TABLE` statement in your code. Look at the code snippet below.

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

In this case, you have added a foreign key constraint ‘customer_id’ column, in the ‘orders’ table to reference the ‘customer_id’ column in the ‘customers’ table.

Creating a Foreign Key Without Checking for Existing Data

When you add a foreign key constraint to a table, the database automatically checks for existing data to ensure consistency with the constraint. However, if you know the data is consistent and want to add a constraint without the consistency check, here’s how to do it.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
NOT VALIDATE;

The NOT VALIDATE command tells the database not to check for existing data. This particular case is helpful in specific instances. For example, when you have massive data and wish to complete the validation process.

Creating a Foreign Key via DELETE/UPDATE

As you create foreign key constraints, you can direct the action to be taken in cases where the referenced row is updated or deleted. In this case, you are using cascading referential integrity constraints to dictate the actions to be taken. They include:

#1. NO ACTION

As with many other databases, the ‘NO ACTION’ rule is the default behavior when you create a foreign key constraint. This means no action is taken when the referenced row is deleted or updated.

The Database Engine raises an error if the foreign key constraint is violated. However, this is not recommended because it can lead to referential integrity issues as the foreign key constraint needs to be enforced. Here’s an example of how to do it:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

#2. CASCADE

The ‘CASCADE’ rule is another option for the ‘ON DELETE’ and ‘ON UPDATE’ actions when creating foreign key constraints. When put in place, it means that whenever a row is updated or deleted in the parent tables, the referenced rows are updated or deleted accordingly. This technique is powerful when maintaining referential integrity. Here is an example:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

You should be keen when using this rule, as it can cause undesirable consequences if not used carefully. You’d want to avoid accidentally deleting too much data or creating circular references. Therefore, only use this option if necessary and with caution.

There are some rules about using CASCADE:

• You cannot specify CASCADE if a timestamp column is either a part of the foreign or referenced key.
• If your table has an INSTEAD OF DELETE trigger, you cannot specify ON DELETED CASCADE.
• You cannot specify ON UPDATE CASCADE if your table has INSTEAD OF UPDATE trigger.

#3. SET NULL

When you delete or update a corresponding row in the parent table, all values making up the foreign key are set to null. This constraint rule demands that foreign key columns be nullable to execute and cannot be specified for tables having INSTEAD OF UPDATE triggers. Here’s an example of how to do it.

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
ON UPDATE SET NULL

In this case, you have set the foreign key column ‘customer_id’ in the “orders” table to be null if the corresponding row in the “customers” table is deleted or updated.

#4. SET DEFAULT

Here, you are setting all the values that make the foreign key default provided that the referenced row in the parent table is updated or deleted.

This constraint executes if all foreign key columns have default definitions. If a column is nullable, its default value is set to NULL. Note that this option cannot be specified for tables with INSTEAD OF UPDATE triggers. Here is an example:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT;

In the case above, you have set the ‘customer_id’ in the “orders” table to its default value, which happens when the corresponding row in the “customers” table is deleted or updated.

Final Words

In this guide, you have had a refresher on primary key constraints and dug into foreign key constraints. You have also encountered several techniques to create foreign key constraints. And while there are many ways to create foreign key constraints, this post has unraveled the methods.

And hoping you have grasped new techniques; you are not limited to combining them. For example, the CASCADE, SET NULL, SET DEFAULT, and NO ACTION constraint methods can be combined on tables with referential relations.

If your table encounters NO ACTION, it returns to other constraint rules. In other cases, a DELETE action can trigger a combination of these rules, and the NO ACTION rule will be run as the last.

Next, check out the SQL cheat sheet.