{"id":2686,"date":"2013-05-22T16:53:39","date_gmt":"2013-05-23T00:53:39","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2686"},"modified":"2024-01-29T20:42:38","modified_gmt":"2024-01-30T03:42:38","slug":"mysql-primary-key","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-primary-key\/","title":{"rendered":"MySQL Primary Key"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL primary key<\/strong> constraint to create the primary key for a table.<\/p>\n\n\n\n

Introduction to the MySQL primary key<\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

A table can have either zero or one primary key, but not more than one.<\/p>\n\n\n\n

Defining a single-column primary key<\/h3>\n\n\n\n

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

CREATE TABLE table_name(\n   column1 datatype PRIMARY KEY,\n   column2 datatype, \n   ...\n);<\/code><\/span><\/pre>\n\n\n
In this syntax, you define the PRIMARY KEY<\/code> constraint as a column constraint<\/em>.<\/p>\n\n\n\n
Additionally, you can put the PRIMARY KEY<\/code> at the end of the column list:<\/p>\n\n\n
CREATE TABLE table_name(\n   column1 datatype,\n   column2 datatype, \n   ...,\n   PRIMARY KEY(column1)\n);<\/code><\/span><\/pre>\n\n\n
In this syntax, you define the PRIMARY KEY<\/code> constraint as a table constraint<\/em>.<\/p>\n\n\n\n
Defining a multi-column primary key<\/h3>\n\n\n\n
If the primary key consists of two or more columns, you need to use a table constraint to define the primary key:<\/p>\n\n\n
CREATE TABLE table_name(\n   column1 datatype,\n   column2 datatype,\n   column3 datatype,\n   ...,\n   PRIMARY KEY(column1, column2)\n);<\/code><\/span><\/pre>\n\n\n
In this syntax, you list the primary key columns inside parentheses, separated by commas, followed by the PRIMARY KEY<\/code> keywords.<\/p>\n\n\n\n
Adding a primary key to an existing table<\/h3>\n\n\n\n
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<\/code> statement:<\/p>\n\n\n
ALTER TABLE table_name\nADD PRIMARY KEY(column1, column2, ...);<\/code><\/span><\/pre>\n\n\n
Removing a primary key<\/h3>\n\n\n\n
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<\/code> statement:<\/p>\n\n\n
ALTER TABLE table_name\nDROP PRIMARY KEY;<\/code><\/span><\/pre>\n\n\n
MySQL PRIMARY KEY examples<\/h2>\n\n\n\n
We’ll explore some examples of defining primary keys.<\/p>\n\n\n\n
1) Defining a single-column primary key example<\/h3>\n\n\n\n
The following example creates a table<\/a> called products<\/code>, which has the id<\/code> column as the primary key:<\/p>\n\n\n
CREATE TABLE products(\n   id INT PRIMARY KEY,\n   name VARCHAR(255<\/span>) NOT NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT INTO products (id, name) \nVALUES \n    (1<\/span>, 'Laptop'<\/span>),\n    (2<\/span>, 'Smartphone'<\/span>),\n    (3<\/span>, 'Wireless Headphones'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
If you attempt to insert a duplicate value into the primary key column, you’ll get an error. For example:<\/p>\n\n\n
INSERT INTO products (id, name) \nVALUES \n  (1<\/span>, 'Bluetooth Speaker'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
ERROR 1062<\/span> (23000<\/span>): Duplicate entry '1'<\/span> for<\/span> key 'products.PRIMARY'<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that MySQL found a duplicate entry 1 for the primary key of the products<\/code> table.<\/p>\n\n\n\n
2) Defining a single-column primary key with AUTO_INCREMENT attribute example<\/h3>\n\n\n\n
The following statements re-create the products<\/code> table with the primary key that uses the AUTO_INCREMENT<\/code> attribute:<\/p>\n\n\n
DROP TABLE products; \n\nCREATE TABLE products(\n   id INT AUTO_INCREMENT PRIMARY KEY,\n   name VARCHAR(255<\/span>) NOT NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, you can insert new rows into the products<\/code> table without having to provide the values for the primary key column. For example:<\/p>\n\n\n
INSERT INTO products (name) \nVALUES \n    ('Laptop'<\/span>),\n    ('Smartphone'<\/span>),\n    ('Wireless Headphones'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL automatically generates sequential integer values for the id<\/code> column when a new row is inserted. <\/p>\n\n\n\n
Here’s the contents of the products<\/code> table:<\/p>\n\n\n
SELECT * FROM products;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+---------------------+\n| id | name                |\n+----+---------------------+\n|  1<\/span> | Laptop              |\n|  2<\/span> | Smartphone          |\n|  3<\/span> | Wireless Headphones |\n+----+---------------------+\n3<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Defining a multi-column primary key example<\/h3>\n\n\n\n
We’ll create a new table called customers<\/code>:<\/p>\n\n\n
CREATE TABLE customers(\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    first_name VARCHAR(255<\/span>) NOT NULL<\/span>,\n    last_name VARCHAR(255<\/span>) NOT NULL<\/span>,\n    email VARCHAR(255<\/span>) NOT NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Suppose each customer has some favorite products and each product is favored by some customers.<\/p>\n\n\n\n
To model this relationship, you need to create a table called favorites<\/code>:<\/p>\n\n\n
CREATE TABLE faviorites(\n    customer_id INT,\n    product_id INT,\n    favorite_at TIMESTAMP DEFAULT<\/span> CURRENT_TIMESTAMP,\n    PRIMARY KEY(customer_id, product_id)\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The favorites<\/code> table has a primary that consists of two columns customer_id<\/code> and product_id<\/code>. <\/p>\n\n\n\n
4) Adding a primary key to a table example<\/h3>\n\n\n\n
The following statement creates a table called tags<\/code> without a primary key:<\/p>\n\n\n
CREATE TABLE tags(\n    id INT,\n    name VARCHAR(25<\/span>) NOT NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
To make the id<\/code> column the primary key, you use the ALTER TABLE ... ADD PRIMARY KEY<\/code> statement:<\/p>\n\n\n
ALTER TABLE tags\nADD PRIMARY KEY(id);<\/code><\/span><\/pre>\n\n\n
5) Removing the primary key from a table<\/h3>\n\n\n\n
The following statement removes the primary key from the tags<\/code> table:<\/p>\n\n\n
ALTER TABLE tags\nDROP PRIMARY KEY;<\/code><\/span><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • A primary key is a unique identifier for a row in a table.<\/li>\n\n\n\n
  • Use the PRIMARY KEY<\/code> constraint to define a primary key for a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Note that in the foreign key tutorial<\/a>, you’ll learn how to define a foreign key for the customer_id<\/code> column that references the id<\/code> column of the customers<\/code> table and a foreign key for the product_id<\/code> column that references the id<\/code> column of the products<\/code> table.<\/p>\n\n\n\n
Keeping track of primary key values manually can be challenging. To simplify this process, MySQL provides the AUTO_INCREMENT<\/a> attribute, which automatically assigns a unique value to the primary key each time you insert a new record.<\/p>\n\n\n\n
When you insert data<\/a> into the products<\/code> table, you need to ensure the uniqueness of values in the id<\/code> column. For example:<\/p>\n\n\n