Handling Foreign Key Constraints in SQL
Written by Robert Greiner on April 06, 2010
Here's a useful tip for handling foreign keys when creating database tables that I seem to forget every time I write SQL statements to create a table. When using foreign keys you need to use the entire key as your foreign key, you can't just use part of it. This example could be easily solved by creating unique IDs but let's just pretend our boss won't let us. The primary key for the Games table consists of both the title of the game and the studio that creates the game. This way, another studio can create a children's game called Dragon Age: Origins. This adds the foreign key constraint
<title , studio>to the Inventory table to ensure the price and quantity assigned to a particular game match the exact entry in the Games table. Note, that it's impossible to create a foreign key on just
Games.titlebecause it is not a complete key. This is pretty introductory level database material but knowing the small details of database implementation will drastically improve your design process.