Difference between Primary and Foreign Key
| Primary Key | Foreign Key |
|---|---|
| A primary key is a column or a set of columns that uniquely identify a row in a table. A primary key should be short, stable and simple. | A foreign key is a field (or collection of fields) in a table whose value is required to match the value of the primary key for a second table. Usually a foreign key is in a table that is different from the table whose primary key it is required to match. A table can have multiple foreign keys. |
| The primary key cannot accept null values. | Foreign key can accept. |
| We can have only one primary key in a table. | We can have more than one foreign key in a table. |
Some other points on primary and foreign keys worth considering:
1. Primary keys are used with foreign keys to enforce referential integrity.
2. Unlike primary key or unique constraint, a foreign key doesn’t have to be unique. Using the Customer/Order example, one customer can have multiple orders but one order has only one customer. This describes a one-to-many relationship. The primary key column of the Customer table is related to the foreign key column of the Order table through a relationship known as a foreign key constraint.
3. One important difference between foreign keys and other kinds of keys is that foreign keys are bi-directional; that is, they have effects not only in restricting the child table to values that exist in the parent, but they also check for child rows whenever we do something to the parent (by doing so, they avoid orphans). The default behavior is for SQL Server to "restrict" the parent row from being deleted if any child rows exist.
Sources:
Sams Teach Yourself SQL Server 2005 Express in 24 Hours by Alison Balter.
Beginning T-SQL with Microsoft SQL Server 2005 and 2008 by Paul Turley, Dan Wood.
Beginning Microsoft SQL Server 2012 Programming by Paul Atkinson, Robert Vieira.
Concepts of Database Management by Philip Pratt, Mary Last.