Primary keys in MySQL
Start your free 7-days trial now!
In MySQL, a primary key is a column or a group of columns that uniquely identifies each row in a table. The column or group of columns that serve as the primary key for a table may not have NULL values. Finally, each table may only have one primary key.
Example
To create a table neighbors and specify the id column as the primary key:
CREATE TABLE neighbors ( id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY (id));
INSERT INTO neighbors (name) VALUES ('alex');INSERT INTO neighbors (name) VALUES ('bob');INSERT INTO neighbors (name) VALUES ('cathy');INSERT INTO neighbors (name) VALUES ('doge');
SELECT * FROM neighbors;
+----+-------+| id | name |+----+-------+| 1 | alex || 2 | bob || 3 | cathy || 4 | doge | +----+-------+
Ensuring unique primary key values
Suppose we tried to add an additional row to the above table with an id of 3:
INSERT INTO neighbors (id, name) VALUES (3, 'Emily');
ERROR 1062 (23000): Duplicate entry '3' for key 'neighbors.PRIMARY'
MySQL is smart in that if we try to add a duplicate value in a primary key column, it will automatically prevent us from doing so and throw an error.
Multi-column primary key
To specify a primary key as a combination of more than one column:
CREATE TABLE neighbors ( id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY (id, name));
For the above table, we can have rows having same id or same name values but not both same id and name.
Surrogate keys vs Natural keys
Surrogate keys
Surrogate keys are primary keys that do not inherently possess any meaning, and are arbitrarily created simply for the purpose of being unique. For instance, in the below table students, the student_id serves as a surrogate key to uniquely identify students in our school:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Natural keys
Natural keys are the opposite of surrogate keys. As the name suggests, natural keys are primary keys that are naturally unique. For instance, email addresses are a type of natural key because they are not something that we have arbitrarily created for use in a database. Another good example is the social security number, which is used in the United States.
