Database Fundamentals #32: Create Unique Constraints with T-SQL

In the last Database Fundamentals post, I explained what a unique constraint was and how you can create them using the GUI. Using TSQL to create a constraint is very similar to the primary key and foreign key constraints that you created in this post. You can use either the ALTER TABLE command or create the constraint when you make the table with the CREATE TABLE command.

Using CREATE TABLE to Make a Unique Constraint

There aren’t any new tables we need for this post, so we’ll create a table that we can drop immediately as soon as we’re done experimenting. You should already be familiar with the CREATE TABLE statement (or follow the link above):

CREATE TABLE dbo.ConstraintTest
(
    TestName VARCHAR(50),
    CONSTRAINT UniqueName
        UNIQUE (TestName)
);

This script creates the table and adds the constraint in a single command. Just like when creating a primary key, you have to supply a name for the constraint. Then you tell it the type of constraint that you’re creating, which is UNIQUE in this case. Finally you define the columns that will define the unique constraint, TestName in the example. That’s it. It really is easier to include the constraint definition in the CREATE TABLE statement. It just makes the code a bit more difficult to read. I find the ALTER TABLE statement a bit more clear.

Drop the table when you’re done experimenting.

ALTER TABLE Works For Unique Constraints

Since you’ve seen the syntax for the constraint in the CREATE TABLE statement and you’ve already used the ALTER TABLE statement to add primary and foreign key constraints, the code for the unique constraint defined below is probably not much of a surprise. One wrinkle that is being tossed in is that the business wants to constrain the names of the people in the database. This constraint is using the FirstName and LastName columns to define the unique values. For very large systems, this would not work. You would need some other columns in the constraint such as BirthDate and maybe the location and time of birth. Depending on where you are in the world, there are some very common names out there.

ALTER TABLE Personnel.Person
ADD CONSTRAINT UniqueFirstNameLastName
    UNIQUE (
               LastName,
               FirstName
           );

We’ll just walk through the code to be sure that everything is clear. The ALTER TABLE statement tells us what we’re doing and we have to define the table we’re altering, Personnel.Person. Then we define the name of the constraint that we’re adding. Finally we declare that the constraint is unique and on two different columns, LastName and FirstName). Based on what we’ve gone over in the past, this should all make sense.

Conclusion

Unique constraints are useful, but they’re not the only way to control and limit the data that gets added to the system. You can define other types of constraints. We’ll start exploring those in more detail in the next post.