Database Fundamentals #29: Create Foreign Keys With Table Designer

The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing data from being removed in the parent table if it’s in the child table. The rules for these relationships are not terribly complex:

• The columns in the two tables must be the same data type, although, if SQL Server can automatically, and correctly, convert the data you can get away with different data types. But don’t do that. It’s begging for an issue. Keep them the same and you won’t have any problems.
• The child values can be nullable, which means that any child data is unknown.
• The child data can also be required, meaning that you have to have the relationship, no matter what. Requiring the data be supplied is the safer mechanism. 
• The parent table can be a primary key or an alternate key, meaning a unique index.

You can remove a foreign key any time you want.

Let’s get started making foreign keys on our tables. First, with the GUI, although, as I’ve noted before, it’s best to learn and then use, T-SQL.

Using the Table Designer for Foreign Keys

In the database we’re building, you have people stored in the Personnel.Person table and you have purchases that these people are making stored in the Finance.FinanacialTransaction table. We have to create a Foreign Key constraint to enforce this relationship.

To use the GUI to make foreign keys, it’s back to the Table Designer window. However, you don’t have to open the table designer and then find the correct button, although that is one way to do this. You can use the Object Explorer to navigate to the Keys folder under the Finance.FinancialTransaction table. Right click on that folder and select “New Foreign Key…” from the context menu. This will immediately open the Table Explorer and the Foreign Key Relationships window.

Because multiple tables and multiple columns are involved in setting up a foreign key, it can seem more complicated than setting up a primary key. This shows the Foreign Key Relationships window with the defaults enabled.

The thing to remember about this window is that it is going to have all the foreign keys for the table you’re working with. You can use the left side of the window to select different foreign keys that are associated with the table you’re working on. Since this is the first foreign key on this table, it’s the only one you can see currently. On the right side of the screen are the properties for the foreign key you selected on the left. You only need to worry about the first two sets of properties, General and Identity. General has two basic values, “Check Existing Data on Create” and “Tables and Columns Specification.” When you create a foreign key, you should allow it to validate the data that exists in the table so that you know if you have bad information already there. That’s why the property “Check Existing Data on Create” is set to “Yes” by default. The second property, “Tables and Columns Specification” is actually a bunch of properties. Click the plus sign next to the property to see them as shown here:

Because SQL Server can’t know which table you’re going to be mapping to, it just defines all the properties with the columns it does have, those from the current table, Finance.FinancialTransaction. You’re going to have to make changes to these. The first two properties describe the table and columns for the child table. The second two properties describe the table and columns for the parent table. Click the ellipsis to open the Tables and Columns window like this:

You can choose to let SQL Server name the foreign key. It will take the tables you define and use them to name the foreign key using FK at the start and separating everything with an underscore. Frankly, I don’t like seeing the FK first, so I usually edit this name, but it’s not required. You do need to change the Primary Key Table. You’ll see the tables in the MonthOfLunches database in the drop down. Select the Personnel.Person table.

When you do this, you’ll see that the column originally selected, TransactionID, immediately below the table is now gone. You’ll need to move down to this column where you’ll see a list of columns for the Personnel.Person table. Select the PersonID column. You’ll now have to select the column that it relates to. On the right side of the screen, on the same line, click on the column and you’ll get another drop down list for all the columns in the Finance.FinancialTransaction table. Select the PersonID column from this table.

If you’ve left the Relationship name as the default, you’ll now notice that it has changed to match the selections you have made, Your screen should now look like this:

You’ve now finished defining the tables and columns so you can click the OK button. This will take you back to the Foreign Key Relationships window, but now, the “Tables and Columns Specification” properties will show the Personnel.Person and PersonID columns instead of what was there before. You still have the opportunity to edit the name of the foreign key from this window in the “Name” property that is grouped beneath “Identity.”

If you were only adding a single foreign key, you could close this window and be done. But this table needs a second foreign key to the Management.Company table. While we’re here, we may as well take care of it too. Click on the Add button at the bottom of the screen. You’ll get another foreign key with all the defaults in place exactly the same as the last one. On your own, change this foreign key to the Management.Company table and the CompanyID column in that table and the Finance.FinancialTransaction table. You’ll now have two primary keys. Click the Close button.

The Foreign Keys window will close, and you’ll have no really obvious indications that anything has been done because this screen won’t change, except a small asterisk will appear on the tab showing that a change has been made on the screen. You need to save the foreign keys that you’ve created. Click on the Save button.

This time, instead of saving the table changes, another window will open as shown here:

Because a foreign key creates a relationship between tables that enforces several behaviors, you’re not simply making a change to the Finance.FinancialTransaction table. You’re also making a change to the Management.Company table and the Personnel.Person table. Because multiple tables are affected by the changes you’re attempting to make, SQL Server stops and asks if you’re sure. You can click No and you’ll be back on the Table Designer window with the changes unsaved. If you click Yes, you may see an hour glass while SQL Server creates and checks the data on your foreign keys. You won’t be able to tell that anything has changed until you expand the Keys folder in the Object Explorer window. Now, you’ll see the primary key you created earlier in the blog post series and the two foreign keys you just created:

Conclusion

While using the GUI to create foreign keys works, it’s just not as easy as creating them with T-SQL. We’ll do that on the next Fundamentals post.