When the primary key of a relation appears in any other relation It is called a?
Foreign keys put the “relational” in “relational database” – they help define the relationship between tables. They allow developers to maintain referential integrity across their database. Foreign keys also help end-users by preventing errors and improving the performance of any operation that’s pulling data from tables linked by indexed foreign keys. Show
On a technical level, a foreign key is a constraint that links a column in one table ( 0) and ensures that a value can be added to 1 only if the same value already exists in 2.For example, a table of customer orders might have a 3 column with a foreign key attribute that links it to the 4 column in a users table. This way, each row in the orders table can be associated with a specific user from the users table — and no orders can enter the system without a valid user being associated with them.How do foreign keys actually work in practice? Let’s get practical, and learn more about foreign keys by looking at how they function in the context of a simple SQL database. In this article, we’ll cover:
Let’s dive in! Our example databaseBelow, we’ve set up a sample database we’ll work with. It represents the sales database of a fictional online bookshop. We can see there are three tables:
Needless to say, the database of a real-life bookshop would be far larger and more complex than this! But this sample database will make it easier to illustrate how foreign keys work, and the principles that apply here will apply in exactly the same way in larger and more complex databases. (If you’d like to try working with this database hands-on, skip to the end of the article for instructions that’ll help you get it set up on a free CockroachDB dedicated cluster in less than five minutes). What’s the difference between primary keys and foreign keys?To understand how foreign keys work, it will help to first understand primary keys. What is a Primary Key?A primary key is a column in a table that is used as a unique identifier for each row. It functions a bit like the row’s address, and is used for the table’s primary index. (Technically, a primary key can be made up of multiple columns, but for our purposes here let’s think of it as a single column). You can think of the primary key as being like a row’s ID number. Just as your government ID identifies you uniquely even if other people share your name or live at your address, a table’s primary key column identifies each row uniquely even if some of the other values in the row are shared with other rows. A table’s primary key column thus must be unique, and it cannot be empty or 8.Consider the 6 table in our sample database. In this table, the primary key is 0, a unique number that identifies each order individually.In fact, in this table 0 is the only column that could be used as the primary key. 4 can contain duplicate data, since the same customer can place more than one order, and 3 can also contain duplicate data, since two different customers might order the same product.Here’s how our database looks with the primary key columns for each table highlighted: Foreign KeysForeign keys link data in one table to the data in another table. A foreign key column in a table points to a column with unique values in another table (often the primary key column) to create a way of cross-referencing the two tables. If a column is assigned a foreign key, each row of that column must contain a value that exists in the ‘foreign’ column it references. The referenced (i.e. “foreign”) column must contain only unique values – often it is the primary key of its table. For a tangible example, let’s look at the 6 table in our database again. The 4 column here corresponds with the 4 column in the 5 table, and the 3 column corresponds with the 3 column in the 7 table.When we’re setting up this table, it would make sense to add foreign key rules to both 1 and 2:
Using these foreign keys saves us from having to store the same data repeatedly – we don’t have to store the user’s name in the 6 table, because we can use 1 to reference that user’s unique row in 4 to get their name and other information about them.But the real purpose of foreign keys is that they add a restriction: entries to the table with a foreign key must have a value that corresponds with the ‘foreign’ table column. This restriction is called a foreign key constraint. Let’s take a look at foreign key constraints in more detail. What is a foreign key constraint?Foreign key constraints are the rules created when we add foreign keys to a table. Foreign key constraints in table 0 link to a column with unique values in table 1 and say that a value in 0’s column is only valid if it also exists in 1’s column.(Note: foreign keys can be composite keys, so the foreign key for one column could be two or more columns in another table. In this article, for the sake of simplicity we’ll focus on linking a single column in one table to a single column in another.) For example, imagine we’ve set up our 6 table with the foreign keys we laid out earlier: 1 references 4 and 2 references 6. These rules mean that:1. Any value entered into CREATE TABLE orders (
order _no INT PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
product_sku INT NOT NULL REFERENCES books(product_sku)ON DELETE CASCADE ON UPDATE CASCADE,
);
CREATE TABLE orders (
order _no INT PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
product_sku INT NOT NULL REFERENCES books(product_sku)ON DELETE CASCADE ON UPDATE CASCADE,
);
|