Back to articles list November 27, 2020 - 7 minutes read What Is a Foreign Key in SQL? Ignacio L. Bisso Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one. Tags: sql learn sql Data Engineering What does a foreign key do in SQL? Why would you need one? Learn the ABCs of foreign keys in five minutes. Foreign keys are a central concept in SQL databases; they allow us to enforce data consistency. Usually they work with primary keys to connect two database tables, like a virtual bridge. All SQL developers need to know what SQL foreign keys are, how they work, what data values are allowed in them, and how they’re created. What Are SQL Foreign Keys? When designing a database, it often happens that an element can’t be fully represented in just one table. In that case, we use a foreign key to link both tables in the database. Suppose we have a database with country data stored in a table called country. We also want to represent the world’s major cities in this database. Initially, we think of creating a single table called city to store the city name, a numeric city_id, and the country where this city is located. For this last attribute, we add a column called country_id that references a record in the country table. Thus, part of the city information – the name of the country where the city is located – is actually stored in the country table. We can say that these tables are related. Below, we can see the SQL to create the country and city tables: CREATE TABLE country ( country_id INTEGER, name VARCHAR(50), population INTEGER ); CREATE TABLE city ( city_id INTEGER, name VARCHAR(50), country_id INTEGER ); Notice that city has a column called country_id that is the link between both tables. The value in the column city.country_id should refer to a valid country_id in the country table. Using the city.country_id value, we can join the city table with country and find the record associated with any city. So far, so good. However, the database doesn't know that both tables are related. Moreover, the database will allow a user to put an invalid value in city.country_id (e.g. something that points to a non-existent record in the country table). A foreign key constraint will inform the database about the relation between the tables. A foreign key constraint is a database constraint that binds two tables. Before we can create a foreign key on the table city, we need a primary key or a unique constraint on the country table. In the code below, we drop and re-create both tables – this time, defining the primary and foreign keys: -- We first create the country table with a primary key DROP TABLE country; CREATE TABLE country ( country_id INTEGER, name VARCHAR(50), population INTEGER, PRIMARY KEY (country_id) ); -- We create the city table with a primary key and a foreign key DROP TABLE city; CREATE TABLE city ( city_id INTEGER, name VARCHAR(50), country_id INTEGER, PRIMARY KEY (city_id), FOREIGN KEY (country_id) REFERENCES country(country_id) ) Once we created the FOREIGN KEY, as we can see in the previous box, the database will verify that the values stored in city.country_id are valid country.country_id values. Before continuing to the next section, I would like to mention the course The Basics of Creating Tables in SQL, where you can learn topics related to tables, foreign key constraints, and primary key constraints. I’d also like to suggest the article What is a Primary Key in SQL?, which provides an interesting entry-level explanation about primary keys. Going Deeper with Foreign Key Constraints Once we have the foreign key constraint between the country and city tables, we can think of country as the parent table and city as the child table. Behind all foreign key validations there is a single rule: The database must ensure that every record in the child table always refers to a valid record in the parent table. There can be several scenarios where this validation is executed: If an INSERT INTO city (the child table) is executed, the database must validate that the value of the city.country_id column refers to an existing record in country (the parent table) If a DELETE FROM country (the parent table) is executed, the database must validate that the city table doesn’t have any records pointing to the country_id being deleted. If an UPDATE for the column country_id on either table (child or parent) is executed, the database must avoid having a record in city (the child side) with a value in country_id that doesn’t exist in the country table (the parent side). Let’s see some examples. Suppose we initially have an empty database and we insert these two records. Both INSERTs will be valid: INSERT INTO country (country_id, name, population) VALUES (1,’United States’,328000000 ); Query returned successfully: one row affected INSERT INTO city (city_id,name,country_id) VALUES (100,’New York’,1 ); Query returned successfully: one row affected However, if we try to insert the following record referring to the country_id 2, we get the following error: INSERT INTO city (city_id,name,country_id) VALUES (101,’Paris’,2 ); ERROR: Insert on table «city» violates foreign key «fk_city_country» DETAIL: The key (country_id)=(2) is not present in the table «country». In the case of a DELETE command, there is a ON DELETE CASCADE clause that can be used when the foreign key is created. If we used the ON DELETE CASCADE clause in the foreign key definition, then any DELETE on the parent table (country) will fire automatic DELETES on the child records instead of returning a validation error. In other words, if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. There are also other clauses like RESTRICT, NO ACTIONS, or SET NULL that can be used when defining a foreign key. The next CREATE TABLE example shows how to create the table city with the CASCADE DELETE activated. DROP TABLE city; CREATE TABLE city ( city_id INTEGER, name VARCHAR(50), country_id INTEGER, PRIMARY KEY (city_id), FOREIGN KEY (country_id) REFERENCES country(country_id) ON DELETE CASCADE ) There is an interesting article about foreign keys where you can see several more examples; have a look for additional info. Multi-Column Foreign Keys In the same way that primary keys can be made up of multiple columns, foreign keys in SQL can also be made up of multiple columns. Suppose there is a brand of luxury watches that maintain records of the owners of all the watches they produce. Every kind of watch model is identified by a model name and a sub-model code. We can see this in the following table, watch: ModelSub-ModelPrice Exact-24zafiro$19200 Exact-24diamond$23000 ClassicArmy$9200 ClassicCity$7500 Here, the primary key for the table watch is a multi-column key formed by the Model and Sub_model columns. The database has another table called owner that stores information on each watch owner’s name, the purchase date, and the watch model and sub-model: Owner NameModelSub-ModelPurchase_date Elena DoeExact-24Zafiro12/23/2003 Mary SmithExact-24Zafiro09/12/2006 Zaira BynzExact-24Diamond09/09/2009 John WatsClassicArmy09/01/2011 Mario ZinClassicArmy07/11/2011 Susan BodExact-24Diamond06/10/2015 Marian DoyleClassicCity03/12/2019 The following SQL code is used to create the primary key on the table watch and the foreign key on the table owner. -- We first create the watch table with the primary key CREATE TABLE watch ( Model VARCHAR(15), Sub_model VARCHAR(15), Price NUMERIC, PRIMARY KEY (model, sub_model) ); -- We create the owner table with a multi-column foreign key CREATE TABLE owner ( Owner_name VARCHAR(50), Model VARCHAR(15), Sub_model VARCHAR(15), Purchase_date DATE, PRIMARY KEY (Owner), FOREIGN KEY (Model, Sub_model) REFERENCES watch(model, sub_model) ); Before closing this section, there’s one more article I’d like to suggest on table creation in SQL. It’s good for those readers who want to review basic table creation concepts. There’s More to Learn About SQL’s Foreign Keys In this article, we explained how foreign keys work in SQL and how to create them. We also showed some SQL codes for foreign key creation. And we explored some possible errors we can get when working with foreign keys. One kind of foreign key we didn’t cover are those that point to their own table. For example, we could have an employee table with the columns emplid and boss_emplid, where emplid is the primary key and boss_emplid is the foreign key. Finally, I would like to suggest the LearnSQL track Create Database Structure, which includes a section on primary and foreign key constraints in a database. It’s a good place to practice the new concepts we’ve talked about. Tags: sql learn sql Data Engineering You may also like How Much Do Data Engineers Earn in 2020? Are you thinking of becoming a data engineer? Find out if it pays off and why it is one of the fastest growing branches of the IT industry. Read more What Is Data Engineering? What’s data engineering and how does it fit in with data science and Big Data? Read this article to find out. Read more Who Is a Data Engineer? Do you know what Data Engineers do? Do you know what skills they need? Read our article to find out more about this relatively new IT position. Read more LearnSQL.com’s New Learning Path: Data Engineering Do you want to become a data engineer? Here is the best path to make your dreams come true. Read more How to Create a Table in SQL How do you create a table in a relational database? Who designs database tables? We’ll discuss the syntax of the SQL CREATE TABLE command and how to use it. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.