This article was written by Brittney Parker, a writer for Girls Write Tech, who specialize in technical writing content. They aim to encourage more female developers to share their knowledge.
There is an overabundance of data types and structures in the database world, and determining which to use is often a hotly-debated topic. Understanding various keys can help you see how to best leverage a specific system for your unique needs.
As the complexity of data structures continues to evolve, databases have shifted to the relational databases and multi-modal databases that are most often used today. Now, we can relate various tables in a meaningful way using foreign keys.
A foreign key is a column (or group of columns) used in a relational database to link data between tables. A foreign key servers to reference the primary key of another existing table.
Today, this tutorial will introduce you to foreign keys and show you how to use them in SQL.
We will learn:
You will learn common techniques like normalization that help to improve databases. After completing this course, you will be able to move onto more advanced database systems and SQL.
Foreign keys are structured into a database as a common component linking together two tables. A foreign key must always reference a primary key elsewhere.
The original table is called the parent table or referenced table, and the referencing table with a foreign key is called a child table.
Foreign key references are stored within a child table and links up to a primary key in a separate table.
The column acting as a foreign key must have a corresponding value in its linked table. This creates referential integrity.
We must be careful when we create tables or alter tables (such as inserting or deleting data from the foreign key column) to avoid altering or destroying the linkage between them.
Say we have two tables called
order. We can use a foreign key to create a relationship between them. In the
orders table, we create a key that references customer (i.e.
CUSTOMER_ID) in the other table.
CUSTOMER_ID in the order table becomes the foreign key that references parent or primary keys in the customer table.
Note: To insert an entry into the order table, the foreign key constraint must be satisfied.
If we try to input into
CUSTOMER_ID using data not present in the customer table, we’ve damaged the integrity of the table’s referential powers.
Bridging data accurately is a prime directive. Software integrations and the ability to securely share data between applications are all dependent on data integrity and database relationships.
This is where constraint comes in. Foreign keys are often constrained to ensure the user cannot take actions that would damage dependency links between tables. This also constrains users from entering invalid data.
We can use foreign key restraints to help maintain referential integrity of our foreign key relationships. There are many referential actions we can use for constraint, including:
Cascade: when deleting primary key values, the matching column in the child table are deleted
Set null: when a referenced row is deleted/altered, the referencing values in the foreign key are set to null
Restrict: Values in the parent table cannot be deleted if they are referred by a foreign key.
Set default: The foreign key values in the child table are set to a default value if the parent table is altered/deleted
When it comes to foreign key constraint naming, we need to follow these general rules:
CONSTRAINTsymbol value is used, and it must be unique in the database.
FOREIGN KEY index_namevalue is used, or a constraint name is generated automatically.
Unlike a foreign key, a primary key works within a relational database to uniquely identify specific records. Primary keys must be unique to the table and are often used as an absolute reference point for other types of database keys.
Foreign keys are used more as a link than as a unique identifier of a specific row or record.
While multiple foreign keys can be utilized within a specific table or relational database, only one primary key is permitted per database. Duplicate values are permitted for foreign keys, as well as null values.
Null values are not permitted for primary keys, and references to any primary keys within a table must be deleted before removing a primary key row/record.
Learn how to design your own databases without scrubbing through videos or documentation. Educative’s text-based courses are easy to skim and feature live coding environments, making learning quick and efficient.
Composite keys within a relational database are used to combine two or more columns within a specific table, creating a unique identifier for that combination of columns.
While technically a candidate key as the composite key verifies uniqueness, composite keys are only formed when the particular column or columns are used in combination with each other.
Like foreign keys, composite keys can be used to link together multiple tables within a relational database. Unlike foreign keys, composite keys can be defined as a primary key during the creation of some SQL tables.
Referential integrity is constrained by foreign keys, ensuring that values in a particular table match values that are found in a different table.
These referential actions reinforce the integrity of the table structure, reducing the possibility of error by ensuring that referenced columns only contain unique sets of values.
Foreign keys can also accept null values, but it’s important to note that this may restrict their ability to protect the integrity of the referenced column, as null values are not checked.
Tip: Best practices indicate using a
NOT NULLconstraint when creating foreign keys to maintain the structural integrity of the database.
Creating a data structure that is flexible and extensible enough for long-term use can become increasingly difficult as data complexity and volume soars. The addition of unstructured data can easily result in errors.
Foreign keys are an extremely valuable component, helping ensure that your database is clear, consistent, and able to rapidly deliver accurate results.
Let’s take a look at some syntax using SQL and MySQL. The following example creates a FOREIGN KEY on the “PersonID” column.
Learn more about different database types here
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Persons(PersonID) );
The following syntax allows us to name FOREIGN KEY constraint:
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
Now let’s get more specific. Below, the
Actors table is the referenced table and called the parent table. Here, the referencing table
DigitalAssets is the child table.
We declare a column as a foreign key in a child table only if the column has an index defined on it. If the column doesn’t have an index it can’t be used as a foreign key.
For our example, we alter our
DigitalAssets and set the
ActorID column to be the foreign key as follows:
ALTER TABLE DigitalAssets ADD FOREIGN KEY (ActorId) REFERENCES Actors(Id);
Now if we add a row in the
DigitalAssets table with an actor ID that doesn’t exist in the
Actors table, an error is reported:
INSERT INTO DigitalAssets VALUES ("www.dummy.url", "instagram", "2030-01-01 00:00:00", 100);
We can also create a foreign key constraint on a table itself. For example, this could be an employees table with a column to identify the manager. Since the manager is also an employee, a row identifying them will also be present.
The manager’s ID will reference the employee ID in the same column, and the employee ID will act as a foreign key.
Congrats! You should now have a good idea how foreign keys can be used in your databases to make them more useful and secure. There is still a lot to learn. Next, you should check out:
To get started with these concepts, check out Educative’s course Database Design Fundamentals for Software Engineers to learn about the fundamental concepts of databases. You’ll learn why and when they’re used, what relational databases are, and entity-relationship diagrams.
If you already have some experience with databases, Educative’s course An Introductory Guide to SQL is another great step. You’ll learn the basics of SQL such as how to create a database, how to insert, query, and update data.
Join a community of more than 1.3 million readers. A free, bi-monthly email with a roundup of Educative's top articles and coding tips.