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.
--Products:
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 --prodctSales: 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!