Manage Learn to apply best practices and optimize your operations.

Set database constraints using SQL Server Management Studio Table Designer

In addition to creating tables and adding columns, SQL Server Management Studio Table Designer can also set constraints.

This is the second of two parts on using SQL Server Management Studio Table Designer. This part focuses on setting...

database constraints; the first looked at creating tables and adding columns.

Adding database constraints to a table is an important part of the table-creation process. For most tables, you'll likely define a PRIMARY KEY constraint, but other types of database constraints might also be needed, such as UNIQUE, FOREIGN KEY, CHECK or DEFAULT.

You've already seen how to add a DEFAULT constraint, which we did when we defined the DateAdded column, so let's look at how to add a PRIMARY KEY constraint, which we'll base on the StoreID and ProductID columns. To create the constraint, select the two columns in the Table Designer grid, right-click somewhere within the upper pane, and then click Set Primary Key. A key icon will be added to the column listings to indicate that a primary key has been added, as shown in Figure 6.

Using SSMS Table Designer to set up a table's primary key
Figure 6. Setting up the table's primary key

That's all you need to do to create the primary key. To view or modify that key's settings, right-click the upper grid once more, but this time click Indexes/Keys to launch the Indexes/Keys dialog box. As you can see in Figure 7, the dialog box displays information about the new primary key, which has been named PK_SpecialtyProducts.

Configuring the table's primary key in SSMS Table Designer
Figure 7. Configuring the table's primary key

At this point, you can stick with the default settings or modify the properties as necessary. For example, you can change the constraint name, the primary key columns, and whether a nonclustered index should be created rather than a clustered one. For this exercise, I changed the name to pk_StoreID_ProductID, but left the other properties with their default values.

You can also create a UNIQUE constraint in the Indexes/Keys dialog box by clicking the Add button. When you click the button, a basic index is added to the dialog box, which in this case is named IX_SpecialtyProducts, as shown in Figure 8.

Adding an index using SSMS Table Designer
Figure 8. Adding an index to the SpecialtyProducts table

Once again, you can change the name as well as modify other settings. For this example, I've changed the name to uq_AltID because the constraint will be created on the AltID column. However, to turn this into a UNIQUE constraint, we have to take a couple more steps. First, we need to actually specify the AltID column. Note, however, the AltID column permits null values. By creating a UNIQUE index on this column, only one null value is permitted at a time. This behavior is unusual for most relational databases, but that's how it works in SQL Server. For this reason, you might reconsider whether you want to permit null values in this column.

To specify the AltID column, select the Columns property and then click the browse button (the ellipses) at the right of the property listing. This launches the Index Columns dialog box, where you can select the columns to participate in the constraint and the sort order for each column, as shown in Figure 9.

Specifying an indexed column in SSMS Table Designer
Figure 9. Specifying the indexed column

After you've selected the AltID column, click OK to close the Index Columns dialog box. In the Indexes/Key dialog box, select the Unique Key option for the Type property. This automatically changes the Is Unique property to Yes. Figure 10 shows what our UNIQUE constraint now looks like with the new name and updated settings.

Creating a UNIQUE constraint using SSMS Table Designer
Figure 10. Creating a UNIQUE constraint on the AltID column

Our next step is to create a FOREIGN KEY constraint on the ProductID column. The foreign key will reference the ProductID column in the Production.Product table. To create the FOREIGN KEY constraint, right-click the upper pane in Table Designer and then click Relationships. When the Foreign Key Relationships dialog box appears, click Add to create the new constraint. Figure 11 shows the dialog box with the initial foreign key, before any properties have been configured.

Adding a foreign key using SSMS Table Designer
Figure 11. Adding a foreign key to the SpecialtyProducts table

At a minimum, we must modify the Tables And Columns Specification property group, which identifies the referenced and referencing columns in the foreign key. Select the property group listing and then click its associated browse button. When the Tables and Columns dialog box appears, select the Product table as the primary key table and the ProductID column from that table. For the SpecialtyProducts table, which is the foreign key table, select the ProductID column. In addition, modify the name of the foreign key, if desired. I went with fk_ProductID. The Tables and Columns dialog box should now look similar to the one shown in Figure 12.

Specifying foreign key columns in SSMS Table Designer
Figure 12. Specifying the foreign key columns

Once you've defined the constraint tables and columns, click OK to close the Tables and Columns dialog box. The Foreign Key Relationships dialog box should reflect your changes, as shown in Figure 13. Notice that the Tables And Columns Specification property group and its individual properties now show the referencing and referenced tables and columns. The name of the constraint has also been updated.

Defining the foreign key in a column using SSMS Table Designer
Figure 13. Defining a foreign key on the ProductID column

At this point, you can add more FOREIGN KEY constraints if desired. For example, you might want to define a foreign key on the StoreID column that references the BusinessEntityID column in the Sales.Store table. But for now, let's move on to CHECK constraints.

To add a check constraint, once again right-click the upper pane in Table Designer and then click Check Constraints. When the Check Constraints dialog box appears, click the Add button to create the new constraint. Figure 14 shows the dialog box with the initial constraint, before any properties have been configured.

Adding a check constraint using SSMS Table Designer
Figure 14. Adding a check constraint to the SpecialtyProducts table

To create the CHECK constraint, you must configure the Expression property. You can either enter an expression directly or click the browse button to launch the Check Constraint Expression dialog box, where you have more room to work on your expression, as shown in Figure 15.

Defining a check constraint's expression in SSMS Table Designer
Figure 15. Defining the CHECK constraint's expression

In this case, the expression I've defined specifies that each AltID value must match the string value defined by the regular expression, which starts with "SP" and ends with five digits. The first digit must be 1 through 9 and each remaining digit must be 0 through 9. As a result, only values that match this format can be inserted into the AltID column. For example, you can insert SP54321, but not SC01234.

Once you've defined your expression, you can set any of the other properties, including Name. In this case, I've renamed the constraint ck_AltID. Figure 16 shows what the CHECK constraint should now look like in the Check Constraints dialog box.

Configuring a check constraint on a column in SSMS Table Designer
Figure 16. Configuring a CHECK constraint on the AltID column

As you can see, Table Designer makes it easy to add CHECK constraints as well as other types of constraints. Given the important role that constraints can play in ensuring the integrity of your data, it's important to get them right, and Table Designer can help you do just that.

Finishing up

Once you've defined your table properties, created your columns and added your constraints, you're ready to save the table definition to the database. To do so, simply click the Save button on the toolbar. The Save dialog box will appear, providing you with a list of tables that are affected by your new table, as shown in Figure 17. In this case, the Product table is included because we defined a foreign key relationship to that table.

Saving a database table in SSMS Table Designer
Figure 17. Saving the SpecialtyProducts table to the database

After you've created your table, you can view it in Object Explorer in SSMS. (You'll likely need to refresh the list of tables in your database.) Figure 18 shows the new table as it is listed in the AdventureWorks2012 database. Notice that each node is expanded to show the columns, keys, constraints, indexes and statistics that have been created.

Viewing SpecialtyProducts table in Object Explorer
Figure 18: Viewing the SpecialtyProducts table in Object Explorer

You can now easily view the T-SQL used to define the table and save the code to a script file. Right-click the table, point to Script Table as, point to CREATE To and then click New Query Editor Window. The code will appear in its own query window. This is a great way for you to see the results of your actions. You can view how the columns and constraints are created and see how table-level properties are configured.

Table Designer might not be for everyone, but for those looking for a way to easily create tables and learn about table definitions in the process, Table Designer could prove a valuable resource. Not only does it let you easily access the various settings available to a table and its columns, but it also provides a user-friendly environment for viewing all your options and letting you see how they interact with each other. Even if you use Table Designer only as a stepping-stone toward learning T-SQL, you'll have much to gain by taking advantage of the features this tool offers.

About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.

Dig Deeper on Microsoft SQL Server Tools and Utilities