SQL 102 – Foreign Keys

In the simplest terms a Foreign Key in one table refers to the Primary Key of another table.  Let’s go back to our demo tables products and productSales.

productName     price    quantity   color
-----------     -----    --------   -----
pencil          0.25     100        yellow
pen             0.99     73         blue
paper           1.00     500        white
Mountain Dew    1.25     8          green

buyer             productName    purchaseDate              qtypurchased    pricePaid
--------------    -----------    ------------              ------------    ---------
Shannon Lowder    paper          2000-01-01 00:00:00.000    2              1.00
Shannon Lowder    pencil         2000-01-05 00:00:00.000    1              0.25
Shannon Lowder    pencil         2000-01-07 00:00:00.000    1              0.25

In this example, the productName is the primary key to the products table.  In the productSales table, productName is the foreign key.  If you recall the JOIN article I wrote, you’ll recall we wrote a query that combined these two tables in order to answer different questions about the sales.  While the columns of a join won’t always be the primary key of one table and a foreign key in the other, if you were to set up the relationships and constraints so that the foreign key column were pointing to a column in another table, you could save your self time in inserts and updates, since you wouldn’t have to manually check to see if a value is already defined in the primary key’s table.

Some would use that as a reason to never define a column as a foreign key.  Since the database would protect you from inserting corrupt or bad data, some would argue that the database is overstepping it’s bounds.  That the application needs to manage the relationship.  I’ve found the best way to argue for defining foreign keys is to run a report showing counts of orphaned and un-related data between two tables.  Then clean the orphaned data out, then run their slowest performing queries.  The increase in speed is usually enough to convince them the change is worthwhile.

Define a Foreign Key During Table Creation

If you had thought of the foreign key at the time you created the productSales table, you could have used:

CREATE TABLE productSales
    buyer VARCHAR(255) NOT NULL
  , productName VARCHAR(255) NOT NULL
  , purchaseDate DATETIME
  , qtyPurchased INT
  , pricePaid DECIMAL(9,2)
CONSTRAINT fk_productSales_Products__productName FOREIGN KEY (productName)
  REFERENCES products(productName)

I’d like to point out my foreign key naming convention.  I use fk_foreignTableName_primaryTableName__columnName if you use more than one columnName, simply add them on underscore separated.  That way when you look at your constraints in SSMS, you can tell what the constraint is, just by it’s name.

Define a Foreign Key After a Table Creation

Most of the time, you forget about the keys until you go to program something on top of the tables. In that case, you can create the constraint in an alter statement.

ALTER TABLE productSales
 ADD CONSTRAINT fk_productSales_Products__productName
 FOREIGN KEY (productName)
 REFERENCES products(productName)

I want you to become comfortable creating foreign keys before I show you how to disable the foreign key constraints.  Learn how establishing these relationships can ease data integrity checks and speed query times.  Once you get comfortable with that, you can learn how to disable the constraint, manipulate the data, then re-establish the constraint.

If you have any questions, please send them in!  I’m here to help you learn more about SQL!

2 Comments on "SQL 102 – Foreign Keys"

  1. Good work! Thank you!
    I always wanted to write in my blog something like that. Can I take part of your post to my site?
    Of course, I will add backlink?

    Regards, Reader


Leave a Reply

Your email address will not be published. Required fields are marked *