AzureFeeds

A foreign key is a situation where we store the primary key of one table in another table. It’s very useful. For example, we might need to know which sales transactions were for which customers.

Again, keep in mind that like primary keys, there could be more than one column in a key. While a foreign key can reference a unique key, it’s almost always the primary key.

In the example shown above, the column OrderID is the primary key of the Orders table. It’s highlighted in red. The column CinemaID is the primary key of the Cinemas table, but it has been stored in the Orders table. It’s highlighted in green. We refer to that as a foreign key. Clearly in a system that handles orders for cinemas, we’d need to know which cinema each order is for.

Foreign keys are used to ensure database integrity. We can still join tables even if foreign keys aren’t declared but they help make sure that the database makes sense. In this case, it means that I can’t have an order for a cinema that doesn’t exist.

NULLability

Note that the CinemaID column in the Orders table is declared as NOT NULL. This would be common, but is not essential. You can declare a foreign key on a key that isn’t NULL. In that case, what we’re saying is that if order has a cinema specified, it must exist.

Either way, we’re insisting that any value that’s in the CinemaID column must match the primary key of a cinema.

Multiple Foreign Keys

While a table can only have a single primary key, it’s common for them to have multiple foreign keys. Note the following example:

In the Cinemas table, the CinemaID is the primary key, but there are three foreign keys: SalesTerritoryID, CinemaGroupID, and CreditRatingID. For each of these columns, a value that matches the SalesTerritories, CinemaGroups, and CreditRatings tables’ primary keys must be provided.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #103 What is a Foreign Key ? appeared first on The Bit Bucket.