Adding a FK to a Table–#SQLNewblogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One thing that helps ensure your data is intact and doesn’t get out of synch in a relational database is a foreign key. You ought to have these as a part of your design, ensuring that a linkage between a parent and child cannot be broken.

This post looks at adding a FK to an existing table. I’ve written about how to do this in the CREATE TABLE statement in another post.

I have two tables set up: Contacts and Status. Both of these have a StatusID column in them. The Status table contains the lookup values, and these are stored in the child table, Contacts.

To add the foreign key, I add a constraint with the ALTER TABLE ADD CONSTRAINT syntax. After this, I use FOREIGN KEY to list the column(s) and then the REFERENCES phrase to point out the parent table and column.

The example is shown here:

ALTER TABLE dbo.Contacts
   ADD CONSTRAINT FK_Contacts_Status_StatusID FOREIGN KEY (StatusID)
      REFERENCES dbo.Status (Statusid)
;

This will give me  a FK that enforces the values in Contacts as existing in Status.

SQLNewBlogger

I had to do this recently and decided to quickly write this up as I had to look up the syntax to be sure I remembered it correctly. Then it took me about 5 minutes to produce this.

It took me almost as long to see if I’d already written about altering a table with a FK.

Do this for your career, and to show interviewers that you know how to handle common data referential integrity tasks.