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.