Database Fundamentals #27: Creating a Primary Key in the Table Designer

Defining primary keys is the hardest part of the operation. You will need to work very closely with the business in order to define exactly what column or columns make a row unique. Often, this will be difficult for a business to define, but you will need to persist in order to be sure that you can properly maintain the integrity of the data being stored. It’s hard for a business to define partly because people just don’t think in terms of “unique values.” They tend to think in terms of pointing at a thing and saying “that’s the one I want.” But invariably there’s a way to uniquely identify almost any concept that business can come up with. You just have to work with the business people to find that definition.

Creating a primary key is technically quite simple. Let’s start with the Table Designer.

Primary Keys through the Table Designer

If you’re not already connected to your server, do so and open the database we’ve been building in the Object Explorer. Navigate to a table in the Object Explorer. Right click on the table and select “Design” from the context menu. This will open the Table Designer window. The table I’m working with already has a field that is a candidate for being a primary key, the TransactionID column. This is a candidate because it is defined as an identity column. Identity values will be generated automatically as rows get inserted. Unless you change the definition of the identity, each new row will get a unique value. Click on the grid to the left of that column in order to select it.

To set that column as a primary key you have a couple of choices. If you right click on the column in the Table Designer window, the context menu will show an option, right at the top, labeled “Set Primary Key.” Selecting this will make the column into a primary key. Another option is to use the Table Designer toolbar. An icon on the toolbar is just a picture of a key. Click on that will set the column, or columns, selected as the primary key. Using either of these methods, make the TransactionID column the primary key for this table. Your results should look like this:

All you have to do now is click the “Save” button and the table will be saved with a primary key. It’s that easy.

There are a few restrictions that you have to take into account. The data already in the table, if any, must be unique, or you’ll get an error. The column can’t allow NULL values or you’ll get an error. But that’s pretty much it. With this, you now have a primary key in place on the Finance.FinancialTransaction table.

If you refresh tables list in the Object Explorer and then expand the columns on the Finance. FinancialTransaction table, you’ll see the primary key in place on the table, and you can expand the Keys folder to see the primary key definition itself as shown here:

By creating the primary key the way we did, we allowed several default behaviors to occur. First, we didn’t provide a name, so SQL Server provided one for us, PK_FinancialTransaction. It’s completely acceptable to use the default names for objects like  a primary key, but some people like to establish their own naming standards. So, there has to be a way to control how a primary key gets created. There is. Right click anywhere in the main Table Designer window. This opens the context menu. From that you can select “Indexes/Keys…” which will open the Indexes/Keys window. Since a primary key has already been created, you should see the PK_FinancialTransaction key in this window:

If there were no primary key on the table, you could define one from this window just by filling out the appropriate information. Some of that information is only available when creating the primary key, such as the Type value that is in gray up above. But the other values can be edited. You can adjust the column or columns that define the key. You can change the name of the key or provide a description. Some of the other properties are fairly complex and won’t be covered here, but one property does need a little attention, “Create As Clustered.” Indexes are constructs that help you speed up data access, among other functions. A primary key is also an index. By default, all primary keys are created as a clustered index. There can only be a single clustered index on a table. The best place to put a clustered index is usually on the column, or columns, that are most frequently used to access data. Often, this is the primary key. So the default behavior makes sense. However, it’s very important that you separate the two. There can be one primary key on a table and there can be one clustered index on a table. But they don’t have to be the same thing. You can have a nonclustered primary key or a clustered index that isn’t the primary key. Clustered and nonclustered indexes are covered in great detail later in the series.

Conclusion

As a learning tool, the Table Designer can walk you through some of the work. However, you really don’t want to get into the habit of relying on it. Instead, you should learn how to do just about everything using T-SQL. The next post in the series will be on doing exactly that, creating a primary key using T-SQL.