T-SQL 101: 102 What is a Primary Key ?

T-SQL 101: 102 What is a Primary Key ?

In the previous post, we saw how CROSS JOIN operations work. Now it’s rare for us to want to have every combination of the rows in each table.

What we usually end up joining on are primary keys and foreign keys. So we should start by defining what primary keys are.

The primary key of a table is a value or combination of values that uniquely identifies a particular row in a table. If we need to refer to a row in a table, and ensure that we’re only talking about one row, it’s how we do that. So how do we identify that one row exactly?

There are two basic rules for this:

  • The combination of values must be unique. (Note that I didn’t say that each value must be unique)
  • None of the values can be NULL. (They all must have a value)

When defining a primary key on a table, you need to ensure that none of the columns in the primary key are defined as being able to be NULL. It’s not enough to have them currently not being NULL. The column definitions must not allow it.

An example of a primary key on a Customers table might be a CustomerID. But it’s important to understand that a key is one or more columns, not just one column.

So a primary key on a Customers table might be the combination of a SalesRegionID and a CustomerID. That would be common where the same CustomerID values are used in different sales regions.

Surrogate vs Natural Keys

One common argument that I won’t get into here is whether it’s better to use surrogate or natural keys. For now, it is important to know the difference though.

A natural key is one that’s related to the business data. You could work out what the key is by using the real business data that’s in the row.

Finding natural keys can be tough. Consider how hard governments find it to identify citizens. There’s not much about a person that can be unique. And to be a good key, you really don’t want it to be a value that can change. That makes it even harder.

A surrogate key is one that’s unrelated to the business data. For example, if I just number every customer with CustomerID values as 1, 2, 3, and so on, this is an example of a surrogate key. There’s nothing about the value 3 that inherently tells you which customer that relates to.

In the image shown above, the CinemaID column is the primary key of the Cinemas table. In Object Explorer in SQL Server Management Studio, you can see the columns (in this case only one) that I’ve highlighted to show the PK as part of their definition.

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.

2025-01-29