MySQL Primary Key

Summary: In this chapter, we will learn how to use MySQL primary key constraint to create a primary key for the table.

Introducing to MySQL primary key

A primary key is a column or a set of columns that uniquely identifies each row in the table. You must follow the rules below when you define a primary key for a table:

• A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.
• A primary key column cannot contain NULL values. It means that you have to declare the primary key column with the NOT NULL attribute. If you don’t, MySQL will force the primary key column as NOT NULL implicitly.
• A table has only one primary key.

Because MySQL works faster with integers, the data type of the primary key column should be the integer e.g., INT, BIGINT. You can choose a smaller integer type: TINYINT, SMALLINT, etc. However, you should make sure that the range of values of the integer type for the primary key is sufficient for storing all possible rows that the table may have.

A primary key column often has the AUTO_INCREMENT attribute that generates a unique sequence for the key automatically. The primary key of the next row is greater than the previous one.

MySQL creates an index named PRIMARY with PRIMARY type for the primary key in a table.

Defining MySQL PRIMARY KEY Constraints

MySQL allows you to create a primary key by defining a primary key constraint when you create or modify the table.

MySQL PRIMARY KEY constraints using CREATE TABLE statement

MySQL allows you to create the primary key when you create the table using the CREATE TABLE statement. To create a PRIMARY KEY constraint for the table, you specify the PRIMARY KEY in the primary key column’s definition.

The following example creates customers table whose primary key is CustomerID column:

1 2 3 4 5 6 7 8 9 10

CREATE TABLE IF NOT EXISTS customers (     CustomerID INT(11) AUTO_INCREMENT,     CustomerName VARCHAR(45) DEFAULT NULL,     ContactName VARCHAR(45) DEFAULT NULL,     Address VARCHAR(200) DEFAULT NULL,     City VARCHAR(100) DEFAULT NULL,     PostalCode VARCHAR(45) DEFAULT NULL,     Country VARCHAR(100) DEFAULT NULL,     PRIMARY KEY (CustomerID)   );

MySQL PRIMARY KEY constraints using ALTER TABLE statement

If a table, for some reasons, does not have a primary key, you can use the ALTER TABLE statement to add a column that has all necessary primary key’s characteristics to the primary key as the following statement:

1 2 3

ALTER TABLE    table_name ADD PRIMARY KEY(primary_key_column);

The following example adds the id column to the primary key.

First, create the sample table without defining the primary key.

1 2 3 4

CREATE TABLE sample(  id INT,  title VARCHAR(255) NOT NULL );

Second, make the id column as the primary key of the sample table.

1 2	ALTER TABLE sample ADD PRIMARY KEY(id);

 In this chapter, we have learnt how to create a primary key for a new table or add a primary key for an existing table.

---

MySQL CREATE TABLE MySQL Foreign Key

---