Newbie? Then an SQL foreign key may be foreign to you.
You may have heard different opinions about SQL foreign keys. If you haven’t, soon you will. Or your experience will affect your view. The main thing to know is, foreign keys are a must in relational databases.
Yet, some developers may remove or ignore foreign keys when facing some complications. So, what to do? To use the foreign key or not to use it? Will there be times when you won’t need to use them?
This guide is for you to see how important this thing is. You will also know some gotchas in code and learn how to fix them. Besides, of course, we’ll use practical examples. There’s nothing you can’t handle.
What is a SQL Foreign Key?
First things first. What is foreign key in SQL? In a few words, it is a key that links 2 tables. Let’s say you have a parent table and a child table. There is some commonality making them a parent and a child – the key that relates these 2 tables.
However, in SQL databases, a foreign key does not only relate to tables. It enforces the relationship. That is why it is called a foreign key constraint.
An error will occur if you try to add a child record with a foreign key value that does not exist in the primary keys of the parent table. Later, we’ll see code samples illustrating this.
Which Tables Should Have SQL Foreign Key Constraints?
Child tables can have foreign keys. One foreign key may be referencing another table. Also, there can be several foreign keys in a “child” table. In SQL Server, foreign key can reference a primary key or a unique key in another table.
How About Self-Reference?
This goes out of the general definition of a foreign key. A self-reference means you can assign a foreign key that references a different column in the same table. SQL Server, MySQL, and Oracle support this.
Self-reference is applicable when you want to create hierarchies like the manager-staff relationship. It is allowed, still, most implementations of foreign keys are between 2 tables.
Later, we’ll have examples.
4 Benefits of Using SQL Foreign Keys
Let’s explore the primary key and foreign key in SQL in detail. What makes foreign keys a must-have to an SQL database? Let’s examine 4 points (here, the constraint syntax won’t matter).
1. Avoid “Missing” Data
“Missing” data are foreign key values from child tables with no associated primary key values from the parent table. They are also referred to as orphaned rows. When it happens, we can say that the database has little or no referential integrity.
With foreign key constraints enforced, “missing” data won’t happen at all. The database engine will not allow deletion of a primary key value that is referenced by another table. Likewise, inserting a foreign key in the child table that is non-existent in the parent table’s primary keys will trigger an error.
What’s the worst thing that can happen if you don’t use the foreign key constraints? Here are a few:
- Customers won’t receive products they paid for.
- Treatment is not administered to patients.
- Missing checklists skip safety precautions.
You can handle this stuff outside the database, but you have to code it. More on this will follow.
Let’s say a developer in your organization handled the same constraint outside of the database. Will he be responsible and fix the problem in production if the code fails? I don’t think so. And what if you are the database admin? Then, you will have to clean their mess. Not so encouraging if you ask me.
2. Avoid Inconsistent Reports
It relates to the first point. If some data are “missing,” inconsistent totals appear in different reports. Details don’t match the summaries. Orphaned rows add up to the totals of the summaries. Meanwhile, the detailed report did not capture orphaned rows because of an inner join to the parent tables.
If it’s your job to keep your database in good condition, you will also clean this mess.
3. No Code Needed to Avoid Orphaned Rows
Foreign key constraints act like self-cleaning agents. Instead of you cleaning the mess, the database does it by not allowing orphaned rows. Foreign key constraints also act as police. They arrest the faulty logic that causes orphaned rows, treating it like a crime done outside of the database.
Do you want a shiny database free of orphaned rows? Of course, you do. If you want to analyze the data someday, you’d be glad you had used foreign keys. This database will be a good source for copying the necessary data to your staging area.
4. Quickly Understand Table Relationships in a Diagram
SQL Server Management Studio has a built-in diagramming tool for your database. Primary and foreign keys make the database diagram informative at a glance. However, it will depend on how many tables with relationships you have included in the diagram.
Diagrams help new team members understand the data structure. For senior teammates, it can be useful too as documentation.
When SQL Foreign Key Can be a “Problem” (plus the Fix)
To migrate old data to a new database, you will insert records in bulk. If the source database has low referential integrity, it will be a pain to insert records from the source. The reason is that foreign key errors pop up here and there.
Is there a fix? You have 2 options.
- Make sure to populate the reference tables or the parent tables first. After that, populate the child tables. One complication is running very slow. In other cases, more foreign key constraint errors occur. If the latter case takes place, you need to reevaluate the sequence of inserts and ensure the primary keys are inserted first. If there is a “slow-running” problem, consider the next option.
- Disable the foreign keys temporarily and enable them after the migration is done (and cleaned). You can do it in SQL Server Management Studio or use T-SQL ALTER TABLE. However, it’s easier said than done. At this point, you need more patience in addition to your wits and will-power. Later on, we’ll find the syntax for disabling and re-enabling foreign keys.
Another thing to consider is using a database as a staging area for OLAP or data analysis. Suppose the source transactional database is clean from orphaned rows. Or you can manage to avoid these rows through code. Then you can choose not to use foreign keys. Foreign keys will slow down bulk inserts and updates, especially on humongous datasets.
3 Easy Ways to Add, Edit, and Delete SQL Foreign Key Constraints
What does it take to add, edit, or delete foreign keys? It’s easy with these 3 tips.
The first two steps use a graphical user interface. Tools like SQL Server Management Studio or dbForge Studio for SQL Server are very good candidates. The third one will use T-SQL code. The choice of a GUI or T-SQL code depends on the situation.
1. Using Table Designer to Add, Edit, and Delete SQL Foreign Key Constraint
It is possible in SQL to add foreign key constraints when create or change a table structure using Table Designer in SSMS. Figure 1 below shows how to access it from the main menu when the table structure is open.
Another option is to right-click anywhere from the table designer and select Relationships from the context menu:
Once you’ve selected Relationships, the Foreign Key Relationships window will pop up:
In the Foreign Key Relationships window, you can choose to add a new foreign key or edit/delete an existing one.
If you choose to add or edit, click to expand the Tables and Columns Specifications. Then click the ellipsis button to define or edit the primary and foreign key tables.
From there, you can indicate the primary and foreign key columns.
After defining the primary and foreign keys, click OK. Then navigate back to the table designer and save the changes.
2. Using Database Diagram to Add, Edit, and Delete SQL Foreign Key Constraint
You can use the Database Diagram to create SQL foreign key constraints. Figure 5 shows how to create a relationship between two tables by clicking the foreign key table and dragging it to the primary key table.
When you release the mouse, the Tables and Columns window will pop up like the one in Figure 4. Then you can indicate the primary and foreign key columns. Then click OK.
To edit an existing relationship, right-click a relationship in the diagram. Then select Properties:
Then, in the Properties window, expand Tables and Columns and click the ellipsis button:
After clicking the ellipsis button, the Tables and Columns window will appear. You can change the primary and foreign key columns (refer to Figure 4 above again).
Meanwhile, deleting a relationship requires right-clicking an existing relationship. Select Delete Relationships from Database and click Yes when prompted.
3. Using T-SQL to Add, Edit, and Delete SQL Foreign Key Constraint
The third way to add a foreign key is through the T-SQL code. You can use SQL CREATE TABLE and add the foreign key constraint. Or you can also use ALTER TABLE to add this constraint after creating the table.
Here is the syntax for using CREATE TABLE:
-- Single-column foreign key
CREATE TABLE Table2
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
col1 INT NULL REFERENCES Table1(col1)
)
GO
After defining the column name and type, you can add REFERENCES to a table and column. The above syntax shows the Table1 table on the col1 column. Note that column names on both tables must be the same to be valid for foreign keys.
The above syntax is for single-column foreign keys. If you need multiple columns as foreign keys, use the FOREIGN KEY clause as below:
-- Multiple-column foreign key
CREATE TABLE Table2
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 VARCHAR(10) NULL
CONSTRAINT FK_Table1_Table2 FOREIGN KEY(col1, col2)
REFERENCES Table1(col1,col2)
)
GO
After creating the table, you can add foreign keys using ALTER TABLE. Here’s the syntax:
ALTER TABLE Table2 WITH CHECK ADD CONSTRAINT FK_Table1_Table2_2 FOREIGN KEY(col3)
REFERENCES Table3(col1)
GO
To delete a foreign key constraint, you can use ALTER TABLE with DROP CONSTRAINT:
ALTER TABLE Table2
DROP CONSTRAINT FK_Table1_Table2_2
GO
Now, we can summarize 3 ways to add, edit, and delete foreign keys:
SQL (MySQL) Foreign Key Constraint Examples
Child Table Having 1 Reference to a Parent Table
-- Single Reference
CREATE TABLE [dbo].[Countries](
[CountryID] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](50) NOT NULL,
[ContinentID] [int] NULL,
[Modified] [datetime] NOT NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[CountryID] ASC
))
GO
ALTER TABLE [dbo].[Countries] WITH CHECK ADD CONSTRAINT [FK_Countries_Continent] FOREIGN KEY([ContinentID])
REFERENCES [dbo].[Continent] ([ContinentID])
GO
ALTER TABLE [dbo].[Countries] CHECK CONSTRAINT [FK_Countries_Continent]
GO
To visualize this relationship, have a look at Figure 9 below:
The ContinentID is the key that relates the two tables together.
Child Table Having Multiple References
The SportsCar table has multiple references to three different tables:
-- Multiple References
CREATE TABLE [dbo].[SportsCars](
[SportsCarID] [int] IDENTITY(1,1) NOT NULL,
[ManufacturerID] [int] NULL,
[StyleID] [int] NULL,
[CountryID] [int] NULL,
[Model] [nvarchar](50) NOT NULL,
[Years] [varchar](50) NOT NULL,
[Notes] [varchar](255) NOT NULL,
[Modified] [datetime] NOT NULL,
CONSTRAINT [PK_SportsCars] PRIMARY KEY CLUSTERED
(
[SportsCarID] ASC
))
GO
ALTER TABLE [dbo].[SportsCars] WITH CHECK ADD CONSTRAINT [FK_SportsCars_Country] FOREIGN KEY([CountryID])
REFERENCES [dbo].[Countries] ([CountryID])
GO
ALTER TABLE [dbo].[SportsCars] CHECK CONSTRAINT [FK_SportsCars_Country]
GO
ALTER TABLE [dbo].[SportsCars] WITH CHECK ADD CONSTRAINT [FK_SportsCars_Manufacturer] FOREIGN KEY([ManufacturerID])
REFERENCES [dbo].[Manufacturers] ([ManufacturerID])
GO
ALTER TABLE [dbo].[SportsCars] CHECK CONSTRAINT [FK_SportsCars_Manufacturer]
GO
ALTER TABLE [dbo].[SportsCars] WITH CHECK ADD CONSTRAINT [FK_SportsCars_Styles] FOREIGN KEY([StyleID])
REFERENCES [dbo].[Styles] ([StyleID])
GO
ALTER TABLE [dbo].[SportsCars] CHECK CONSTRAINT [FK_SportsCars_Styles]
GO
Here is how it appears in a database diagram:
Self-Reference
Position Hierarchies show self-reference in the following table:
CREATE TABLE [dbo].[Ranks](
[RankId] [int] IDENTITY(1,1) NOT NULL,
[Rank] [varchar](50) NOT NULL,
[RankLevel] [smallint] NOT NULL,
[RankParentId] [int] NULL,
CONSTRAINT [PK_Ranks] PRIMARY KEY CLUSTERED
(
[RankId] ASC
)) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ranks] WITH CHECK ADD CONSTRAINT [FK_Ranks_Ranks] FOREIGN KEY([RankParentId])
REFERENCES [dbo].[Ranks] ([RankId])
GO
ALTER TABLE [dbo].[Ranks] CHECK CONSTRAINT [FK_Ranks_Ranks]
GO
The diagram of this self-reference is simple. The line points to the same table in self-reference.
With ON UPDATE and ON DELETE
With ON UPDATE CASCADE, updating a primary key column value will update the foreign key values in related tables as well. Meanwhile, when you use ON DELETE CASCADE, deleting a primary key will also delete foreign keys. The default for ON UPDATE and ON DELETE is NO ACTION.
Here’s an example of UPDATE and DELETE CASCADE:
ALTER TABLE [dbo].[Countries] WITH CHECK ADD CONSTRAINT [FK_Countries_Continent] FOREIGN KEY([ContinentID])
REFERENCES [dbo].[Continent] ([ContinentID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
Disabling an SQL Foreign Key Constraint
The following will disable an existing foreign key constraint. Note that the relationship still exists.
ALTER TABLE [dbo].[SportsCars] NOCHECK CONSTRAINT [FK_SportsCars_Country]
GO
This is not the default, and it is not recommended. But to speed up bulk inserts and updates, you can temporarily disable the foreign key like the one above. After you’re done, you must switch it back using CHECK CONSTRAINT.
ALTER TABLE [dbo].[SportsCars] CHECK CONSTRAINT [FK_SportsCars_Country]
GO
Gotchas and Fixes
This section will show you what happens when you INSERT, UPDATE, or DELETE records with foreign keys. This also assumes that foreign keys are not disabled with NOCHECK CONSTRAINT. This will help you when you encounter these common problems.
On INSERT
-- This will cause an error because countryID = 47 does not exist in the Countries table
INSERT INTO SportsCars
(ManufacturerID, StyleID, CountryID, Model, Years, Notes)
VALUES (108, 10, 47, 'F2', '2021', 'Limited Edition')
GO
Here’s the error message:
Msg 547, Level 16, State 0, Line 56
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SportsCars_Country". The conflict occurred in database "Vehicles", table "dbo.Countries", column 'CountryID'.
The statement has been terminated.
The Fix: Add CountryID = 47 into the Countries table first. Then, re-run the INSERT statement above. The sequence starts with inserting records into the parent table, and then into the child table.
On UPDATE
-- Update CountryID to 47 will trigger an error.
UPDATE SportsCars
SET CountryID = 47
WHERE ManufacturerID = 108
GO
Here’s the UPDATE error:
Msg 547, Level 16, State 0, Line 60
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_SportsCars_Country". The conflict occurred in database "Vehicles", table "dbo.Countries", column 'CountryID'.
The statement has been terminated.
The Fix: Add CountryID = 47 into the Countries table. Then, re-run the UPDATE statement.
On DELETE
-- This will trigger an error because ManufacturerID = 108 is referenced in the SportsCars table
DELETE FROM Manufacturers
WHERE ManufacturerID = 108
This code will trigger the error like below:
Msg 547, Level 16, State 0, Line 64
The DELETE statement conflicted with the REFERENCE constraint "FK_SportsCars_Manufacturer". The conflict occurred in database "Vehicles", table "dbo.SportsCars", column 'ManufacturerID'.
The statement has been terminated.
The Fix: Delete the corresponding records from the SportsCars table with ManufacturerID = 108. Then, re-run the DELETE statement above. Another way is to enable ON DELETE CASCADE if applicable. The sequence starts with deleting records from the child tables, and then – from the parent table.
Takeaways
So, are foreign keys still foreign to you?
Let’s have a recap of what we have learned so far.
- Foreign keys link two tables (or one table when using self-reference). You need them to ensure referential integrity.
- You can use either a GUI tool or T-SQL to add, edit, or delete the foreign key constraints.
- For GUI tools, you can use SQL Server Management Studio or dbForge Studio for SQL Server. Both offer database diagrams and table designers to create tables with primary and foreign keys.
- CREATE TABLE and ALTER TABLE are suitable for adding and deleting the foreign key constraints.
- You can temporarily disable foreign keys with NOCHECK CONSTRAINT in ALTER TABLE. This will speed up bulk inserts and updates. But make sure to enable it back with CHECK CONSTRAINT.
- To avoid gotchas with foreign keys, make sure to follow the right sequence. For INSERT and UPDATE, insert first into the parent table, then into the child tables. For DELETE, delete the child records first, then delete parent records.
Would you like to add something to help newbies master foreign keys? The Comments section is open for your bright ideas. If you like this post, please share it on your favorite social media platforms.
Tags: foreign key, sql server Last modified: September 24, 2021
