Purpose
Primary keys define how to unique identify one piece of data in a table. As such, it is a good practice for all tables to define a primary key.Technical Details
Primary keys are a type of constraint that is defined for a table. Tables can not have a duplicate or NULL primary key and the database will enforce this. Tables refer to each other using primary keys. When they do, a constraint called foreign key is defined.Sequence Numbers
It is very common to define an "id" column for every table as a primary key. These are typically auto-incrementing sequence numbers that start 1,2,3... In many cases, it is possible to use actual data as a primary key but this tends to be more troublesome. It can also be less efficient as primary keys often get stored in all kinds of indexes and keeping them small is a good idea. For example, you could identify the citizens of a city with a combination of their name and phone number but this tends to more problematic than simply defining an auto-incrementing id. For example, phone numbers change and get reassigned to new people.Example
Consider a Stadium database that has a Seat table with a unique entry for every seat in the stadium. A seat could be identified by a combination of its section, row and seat number. Since this is kinda complicated, the database architect might just define a primary key called "id" that is numbered 1,2,3..| Overview: Primary Key | ||
Type | ||
Definition (1) | A column or list of columns that uniquely identify a row in a table. | |
Definition (2) | A constraint defined for a table that indicates the column or columns that uniquely identify a row. | |
Related Concepts | ||
