Join Adam Wilbert for an in-depth discussion in this video Creating a foreign key relationship in SSMS, part of Database Foundations: Storage.
- Creating a foreign key on an already existing table using SQL Data Definition Language is nearly identical to adding a primary key to a table. Let's add another Foreign Key Relationship between the CustomersCreditCard table and the CreditCardDetails table. First, let's go ahead and explore both of those here inside of our Object Explorer. In the CustomerCreditCards table, we have the fields, CustomerID and CreditCardNumber. And in the CreditCardDetails table, I've got another field here called CreditCardNumber as well. In the CreditCardDetails table, the CardNumber is the primary key.
Each credit card in our system will appear there a single time. In the CustomerCreditCard table though, we're mapping customers to the cards that they use. We might see the same card there multiple times if it's attached to several different customers. In this case, the CustomerCreditCard table wil be the Child table in the relationship. Since it's on the Many side of the One to Many relationship, we'll add the Foreign Key Constraint there. In order to add a Foreign Key Constraint, we need to alter the CustomerCreditCards table. So let's go ahead and start up a New Query window here, and the first thing we're going to do is type in the DDL statement Alter Table, and then the name of the table we want to alter, which is the CustomerCreditCards table.
On the next line we're going to add in a new constraint, and then we'll name the constraint FK_, the name of the Child table, and then an underscore, followed by the name of the Parent table. In this case, CustomerCreditCards_CreditCardDetails. Then we're going to specify which kind of constraint we're going to create. In this case, it's a Foreign Key Constraint. And then in parentheses we'll specify which field from the CustomerCreditCards table we're going to provide the link on.
In this case, that's the CreditCardNumber field. On the next line, we'll specify which table we're connecting to, so we'll say References, and then the table we're connecting to which is the CreditCardDetails table, and then in parentheses, the field in the CreditCardDetails table which is also CreditCardNumber. So that's our completed Alter statement. It's going to add the Constraint Name here, and we've got the names of the fields that are going to be connected together.
Now by default, the Referential Integrity Protections, they're already going to be enabled for this Foreign Key Relationship. If we wanted to turn on Cascade Updates and Cascade Deletes though, we can add two additional lines. In this case, I can type in On Delete Cascade and On Update Cascade. We'll finish our statment with the semicolon at the very end, and once I press the Execute button, we should get a message saying that the "Command was completed successfully." And if we take a look at our CustomerCreditCards table, which is this one right here, I'll right-click on it and say Refresh, and then come into the Keys folder, and we should see the new ForeignKey Relationship between the CustomerCreditCards and the CreditCardDetails section right there.
So that's how you add a foreign key constraint to an existing table. Just like with the primary key constraints, if you're building a new table with a Create Table statement, then you already know that it's going to include a foreign key. You can build that Add Constraint line right into the original Creation statement as well.
Note: This course will also prepare certification candidates for the "Understanding data storage" domain of the Microsoft Technology Associate (MTA) Exam 98-364, Database Administration Fundamentals. Find out more at https://www.microsoft.com/learning/en-us/exam-98-364.aspx.
- Reducing redundancy with normalization
- Fixing partial dependencies
- Creating primary keys
- Linking tables with foreign keys
- Creating clustered and nonclustered indexes