MySQL Primary Key (original) (raw)

Summary: in this tutorial, you will learn how to use the MySQL primary key constraint to create the primary key for a table.

Introduction to the MySQL primary key

In MySQL, a primary key is a column or a set of columns that uniquely identifies each row in the table. A primary key column must contain unique values.

If the primary key consists of multiple columns, the combination of values in these columns must be unique. Additionally, a primary key column cannot contain NULL.

A table can have either zero or one primary key, but not more than one.

Defining a single-column primary key

Typically, you define a primary key for a table when you create the table. Here’s the syntax for defining the primary key that consists of one column:

CREATE TABLE table_name( column1 datatype PRIMARY KEY, column2 datatype, ... );

In this syntax, you define the PRIMARY KEY constraint as a column constraint.

Additionally, you can put the PRIMARY KEY at the end of the column list:

CREATE TABLE table_name( column1 datatype, column2 datatype, ..., PRIMARY KEY(column1) );

In this syntax, you define the PRIMARY KEY constraint as a table constraint.

Defining a multi-column primary key

If the primary key consists of two or more columns, you need to use a table constraint to define the primary key:

CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..., PRIMARY KEY(column1, column2) );

In this syntax, you list the primary key columns inside parentheses, separated by commas, followed by the PRIMARY KEY keywords.

Adding a primary key to an existing table

If an existing table does not have a primary key, you can add a primary key to the table using the ALTER TABLE ... ADD PRIMARY KEY statement:

ALTER TABLE table_name ADD PRIMARY KEY(column1, column2, ...);

Removing a primary key

In practice, you’ll rarely remove a primary key. However, if you want to do so, you can use the ALTER TABLE ... DROP PRIMARY KEY statement:

ALTER TABLE table_name DROP PRIMARY KEY;

MySQL PRIMARY KEY examples

We’ll explore some examples of defining primary keys.

1) Defining a single-column primary key example

The following example creates a table called products, which has the id column as the primary key:

CREATE TABLE products( id INT PRIMARY KEY, name VARCHAR(255) NOT NULL );Code language: PHP (php)

When you insert data into the products table, you need to ensure the uniqueness of values in the id column. For example:

INSERT INTO products (id, name) VALUES (1, 'Laptop'), (2, 'Smartphone'), (3, 'Wireless Headphones');Code language: JavaScript (javascript)

If you attempt to insert a duplicate value into the primary key column, you’ll get an error. For example:

INSERT INTO products (id, name) VALUES (1, 'Bluetooth Speaker');Code language: JavaScript (javascript)

Error:

ERROR 1062 (23000): Duplicate entry '1' for key 'products.PRIMARY'Code language: JavaScript (javascript)

The output indicates that MySQL found a duplicate entry 1 for the primary key of the products table.

Keeping track of primary key values manually can be challenging. To simplify this process, MySQL provides the AUTO_INCREMENT attribute, which automatically assigns a unique value to the primary key each time you insert a new record.

2) Defining a single-column primary key with AUTO_INCREMENT attribute example

The following statements re-create the products table with the primary key that uses the AUTO_INCREMENT attribute:

`DROP TABLE products;

CREATE TABLE products( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL );`Code language: PHP (php)

Now, you can insert new rows into the products table without having to provide the values for the primary key column. For example:

INSERT INTO products (name) VALUES ('Laptop'), ('Smartphone'), ('Wireless Headphones');Code language: JavaScript (javascript)

MySQL automatically generates sequential integer values for the id column when a new row is inserted.

Here’s the contents of the products table:

SELECT * FROM products;

Output:

+----+---------------------+ | id | name | +----+---------------------+ | 1 | Laptop | | 2 | Smartphone | | 3 | Wireless Headphones | +----+---------------------+ 3 rows in set (0.00 sec)Code language: JavaScript (javascript)

3) Defining a multi-column primary key example

We’ll create a new table called customers:

CREATE TABLE customers( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL );Code language: PHP (php)

Suppose each customer has some favorite products and each product is favored by some customers.

To model this relationship, you need to create a table called favorites:

CREATE TABLE faviorites( customer_id INT, product_id INT, favorite_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(customer_id, product_id) );Code language: PHP (php)

The favorites table has a primary that consists of two columns customer_id and product_id.

Note that in the foreign key tutorial, you’ll learn how to define a foreign key for the customer_id column that references the id column of the customers table and a foreign key for the product_id column that references the id column of the products table.

4) Adding a primary key to a table example

The following statement creates a table called tags without a primary key:

CREATE TABLE tags( id INT, name VARCHAR(25) NOT NULL );Code language: PHP (php)

To make the id column the primary key, you use the ALTER TABLE ... ADD PRIMARY KEY statement:

ALTER TABLE tags ADD PRIMARY KEY(id);

5) Removing the primary key from a table

The following statement removes the primary key from the tags table:

ALTER TABLE tags DROP PRIMARY KEY;

Summary

Was this tutorial helpful?