search
Search
Publish
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe: "Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
share
thumb_up_alt
bookmark
arrow_backShare
Twitter
Facebook
thumb_up
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

Primary keys in MySQL

Database
chevron_right
MySQL
chevron_right
Common questions
chevron_right
Articles
schedule Mar 9, 2022
Last updated
local_offer MySQL
Tags

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.

robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...