Primary keys

A primary key is a column or a set of columns that uniquely identifies each record in a database table. It is usually created when the table is created, but it can also be added to an existing table that does not have one.

Here are the rules you should be familiar with when creating a primary key for a table:

a primary key must contain unique values.
a primary key column cannot contain NULL values.
each table can have only one primary key.

Here is an example. Let’s say that we want to create a table called buyers, with the id column serving as the primary key. The table should have three other columns: username, city, and country. Here is the syntax we would use:

CREATE TABLE buyers(
 id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(40),
 city VARCHAR(255),
 country VARCHAR(255)
 );

The AUTO_INCREMENT attribute causes MySQL to set a unique value for the id column in every row. Since no value was specified for the AUTO_INCREMENT column, MySQL will assign sequence numbers automatically.

Let’s insert some records into our table:

mysql> INSERT INTO buyers (username, city, country) VALUES ('john','London','UK');
 Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO buyers (username, city, country) VALUES ('mark','Berlin','Germany');
 Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO buyers (username, city, country) VALUES ('alejandra','Madrid','Spain');
 Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM buyers;
 +----+-----------+--------+---------+
 | id | username | city | country |
 +----+-----------+--------+---------+
 | 1 | john | London | UK |
 | 2 | mark | Berlin | Germany |
 | 3 | alejandra | Madrid | Spain |
 +----+-----------+--------+---------+
 3 rows in set (0.00 sec)

Notice how the AUTO_INCREMENT attribute forced the id column to start with 1, and to increment by 1 for each new record.

Primary keys

MySQL Course