Primary keys in MySQL
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.
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
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
Surrogate keys vs Natural 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:
The above sample table can be created using the code here.
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.