There are actually a couple of ways to create a primary key with T-SQL. You can use the ALTER TABLE script to add a primary key to an existing table, or you can create a primary key as part of the table definition directly. There’s very little difference in the outcome. I’ll show you both methods and you can decided for yourself which one works better for your style of coding.
ALTER TABLE for a Primary Key
This T-SQL statement will alter the table Management.Address to create a primary key. Notice that I’m supplying most things. There are slightly fewer defaults for you to take advantage of when compared to the GUI.
ALTER TABLE Management.Address
ADD CONSTRAINT PK_Address
PRIMARY KEY (AddressID);You have to tell it which table you’re altering, which just makes sense. Then we’re adding a very particular type of constraint called a primary key with a name, PK_Address. The column that it’s being placed on is inside the parenthesis.
Because I didn’t define whether or not this is clustered, it will automatically be created as clustered if there is not yet a clustered index on the table. If you wanted to make it a nonclustered primary key you can tell it that like this:
ALTER TABLE Management.Address
ADD CONSTRAINT PK_Address
PRIMARY KEY NONCLUSTERED (AddressID);You can’t run this script if you already ran the last one because the primary key is in place. But, you can remove a primary key if you need to. It uses the ALTER TABLE script as well:
ALTER TABLE Management.Address
DROP CONSTRAINT PK_Address;But, you can’t drop a primary key if it’s referenced by a foreign key. You’ll have to drop all the foreign keys first. If you’ve dropped the primary key on the Management.Address table, please recreate it as a clustered index.
Once you’ve created a primary key using T-SQL, you won’t see it automatically in Object Explorer. You’ll have to refresh it as you’ve done before. While you can see the column or columns that have been defined as the primary key by looking at the column list in Object Explorer to find the column with the key icon next to it, you can’t tell if that primary key is clustered. You can’t tell anything else about the primary key either. The best way to examine the primary key is too look at the T-SQL used to create it. You can get the T-SQL back out by right clicking on the primary key in the Keys folder and selecting “Script Key as†to get another context menu, then “Create To†and finally select a location. For this example use “new Query Editor Window†but you could have exported it directly to file, copied it to the clipboard so you can paste it somewhere else, or move it directly into an Agent Job (more on the SQL Agent in later blog posts). You can see these menu choices here:
As you can see, there are a lot of ways to generate different kinds of scripts. If you script out the object we just created, don’t be surprised when it looks a little different:
USE [MyDatabase]
GO
/****** Object: Index [PK_Address] Script Date: 8/20/2020 5:33:34 PM ******/
ALTER TABLE [Management].[Address] ADD CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GOThis is the same index that was created in the script above, but the full set of options that are provided by default get listed out when SQL Server generates the script. These are not needed under normal circumstances and the defaults are just fine. The GO commands are mechanisms for making a query execute immediately rather than being part of a larger batch. Note that the generated code does not include a semi-colon to terminate the statements even though this is considered a best practice. You really can concentrate on creating the primary key without worrying about all these different settings, but it’s good to know they’re there should you run into circumstances where you need to use them.
Primary Key on a CREATE TABLE statement
Some of the other tables in our design we’ve been creating throughout this blog series don’t have columns to support data relationships. For example, the Personnel.Person table and the Management.Company table should be related in some fashion to the Management.Address table. Let’s fix that by creating a new table. Because a person, or a company, can have multiple addresses, instead of simply having a direct relationship to the Management.Address table with a column in either of the respective tables, we’re going to create what’s called an interim table to set up a many-to-many relationship. That is to say, many different addresses can be associated with many different people or companies. The first interim table will be for the Personnel.Person table. This script will create the table and put a primary key into place at the same time:
CREATE TABLE Personnel.PersonAddress
(PersonID INT NOT NULL,
AddressID INT NOT NULL,
CONSTRAINT PK_PersonAddress
PRIMARY KEY CLUSTERED (PersonID,AddressID)
);The beginning of the statement should be completely familiar to you. It’s a standard CREATE TABLE statement. The two columns are defined exactly as normal. After that, another comma is added and then the primary key constraint definition is applied. Notice that the syntax is exactly the same as that which you used with the ALTER TABLE statement. That’s because this is just another way of creating the exact same constraint. There is no fundamental difference except where you are performing the action. Also note, this a key that consists of two columns. A key with more than one column is referred to as a compound key. The compound key in this case is necessary because we want to ensure that we can add more than one address for a person, but that we don’t duplicate addresses for a person. Also, note, there is no artificial key on this table. Technically this is a table with a natural key.
If you refresh the Object Explorer you can see the new table that you’ve created and the fact that it has multiple columns that make up the key as shown here:
Even though you created this primary key as a part of the table definition, a separate object is created in the Keys folder. This primary key is exactly the same as any other, you just took advantage of a syntax short-cut to create it as part of the table. All the limits and behaviors discussed so far for primary keys apply to one created using the CREATE TABLE statement. They are no different.
Conclusion
You’ve now built half of what’s necessary for referential integrity within your database. You’ve established primary keys on all of your tables. Remember the concept of referential integrity requires two things, a unique value on the parent and a relationship to a child table through that unique value. Time to start enforcing relationships through the addition of foreign keys.