Created: February-09, 2022 In this tutorial, Our goal is to explore
the concept of multiple primary keys of a table in MySQL. Many-a-times, businesses, and organizations must assign certain columns as the primary key. This primary key has multiple purposes and reasons to beset its setup. It is essentially used to ensure that each column entry assigned as the primary key is unique. If more than one column has been assigned as the primary key, then the combination of values across these should be unique. Note that a primary key cannot possess
Null values in its column. We will certainly get an error if we push or insert Null values in this column. Sometimes, it becomes necessary for organizations to get rid of this key to insert multiple similar values or null values. The table can have more than one primary key. These additional keys are called composite primary keys. To be precise, multiple
primary keys are not assigned to columns, but multiple columns can be described while stating the primary key. Let us understand how this key works and assign multiple columns to the primary key. Before we begin, we create a dummy dataset to work with. Here we create a table, student_details_table, along with a few rows. Here as we can see,
we have set the stu_id and stu_firstName as the primary key for our table student_details. This would ensure that the values in this column cannot be duplicated or NULL. The above query creates a table with rows with first and last names. To view the entries in the data, we make use of the following code. The query mentioned above would give the following output. Now let us use the DESCRIBE statement and see if we have more than one column associated with the primary key. In our case, we would need to write the following query to understand the details of the table student_details_table. This statement would help us fetch the table’s intricate details like the data type associated with each column, different columns and their names, the keys associated with each column, and any extra information relevant to
the table. The table above shows that the fields stu_id and stu_firstName are considered primary keys.Create Table Using student_details_table in MySQL
Use the DESCRIBE Statement Shows the
Structure of Table in MySQL
One might need to do this because many a time, businesses need to maintain records such that there exist no duplicate combinations of certain columns.
For example, suppose a product-based company needs to limit customer orders and the number of products associated with the customer every day.
In that case, they might need to set two primary keys as customer id and product id to match the data and perform any operation necessary without any duplicate combination.
Therefore, with the help of the queries above, we can efficiently set multiple primary keys to a table in MySQL.
Related Article - MySQL Key
Summary: in this tutorial, you will learn how to use MySQL primary key constraint to create the primary key for a table.
Introduction to MySQL primary key
A primary key is a column or a set of columns that uniquely identifies each row in the table. The primary key follows these rules:
- 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 have NULL values. Any attempt to insert or update NULL to primary key columns will result in an error. Note that MySQL implicitly adds a NOT NULL constraint to primary key columns.
- A table can have one an 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. And you should ensure sure that value ranges of the integer type for the primary key are sufficient for storing all possible rows that the table may have.
A primary key column often has the AUTO_INCREMENT attribute that automatically generates a sequential integer whenever you insert a new row into the table.
When you define a primary key for a table, MySQL automatically creates an index called PRIMARY.
MySQL PRIMARY KEY examples
The PRIMARY KEY constraint allows you to define a primary key of a table when you create or alter table.
1) Define a PRIMARY KEY constraint in CREATE TABLE
Typically, you define the primary key for a table in the CREATE TABLE statement.
If the primary key has one column, you can use the PRIMARY KEY constraint as a column constraint:
CREATE TABLE table_name( primary_key_column datatype PRIMARY KEY, ... );
When the primary key has more than one column, you must use the PRIMARY KEY constraint as a table constraint.
CREATE TABLE table_name( primary_key_column1 datatype, primary_key_column2 datatype, ..., PRIMARY KEY(column_list) );
Code language: SQL (Structured Query Language) (sql)In this syntax, you separate columns in the column_list by commas (,).
The PRIMARY KEY table constraint can be used when the primary key has one column:
CREATE TABLE table_name ( primary_key_column datatype, ... , PRIMARY KEY(primary_key_column) );
Code language: SQL (Structured Query Language) (sql)The following example creates a table named users whose primary key is the user_id column:
CREATE TABLE users( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(40), password VARCHAR(255), email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)This statement creates the roles table that has the PRIMARY KEY constraint as the table constraint:
CREATE TABLE roles( role_id INT AUTO_INCREMENT, role_name VARCHAR(50), PRIMARY KEY(role_id) );
Code language: SQL (Structured Query Language) (sql)In case the primary key consists of multiple columns, you must specify them at the end of the CREATE TABLE statement. You put a comma-separated list of primary key columns inside parentheses followed the PRIMARY KEY keywords.
The following example creates the user_roles table whose primary key consists of two columns: user_id and role_id. It defines the PRIMARY KEY constraint as the table constraint:
CREATE TABLE user_roles( user_id INT, role_id INT, PRIMARY KEY(user_id,role_id), FOREIGN KEY(user_id) REFERENCES users(user_id), FOREIGN KEY(role_id) REFERENCES roles(role_id) );
Code language: SQL (Structured Query Language) (sql)Note that the statement also created two foreign key constraints.
2) Define PRIMARY KEY constraints using ALTER TABLE
If a table, for some reasons, does not have a primary key, you can use the ALTER TABLEstatement to add a primary key to the table as follows:
ALTER TABLE table_name ADD PRIMARY KEY(column_list);
The following example adds the id column to the primary key.
First, create the pkdemos table without a primary key.
CREATE TABLE pkdemos( id INT, title VARCHAR(255) NOT NULL );
Code language: SQL (Structured Query Language) (sql)Second, add a primary key to the pkdemos table using the ALTER TABLE statement:
ALTER TABLE pkdemos ADD PRIMARY KEY(id);
Code language: SQL (Structured Query Language) (sql)If you add a primary key to a table that already has data. The data in the column(s), which will be included in the primary key, must be unique and not NULL.
PRIMARY KEY vs. UNIQUE KEY vs. KEY
KEY is the synonym for INDEX. You use the KEY when you want to create an index for a column or a set of columns that is not the part of a primary key or unique key.
A UNIQUE index ensures that values in a column must be unique. Unlike the PRIMARY index, MySQL allows NULL values in the UNIQUE index. In addition, a table can have multiple UNIQUE indexes.
Suppose that email and username of users in the users table must be unique. To enforce thes rules, you can define UNIQUE indexes for the email and username columns as the following statement:
Add a UNIQUE index for the username column:
ALTER TABLE users ADD UNIQUE INDEX username_unique (username ASC) ;
Code language: SQL (Structured Query Language) (sql)Add a UNIQUE index for the email column:
ALTER TABLE users ADD UNIQUE INDEX email_unique (email ASC) ;
Code language: SQL (Structured Query Language) (sql)In this tutorial, you have learned how to create a primary key for a new table or add a primary key to an existing table.
Was this tutorial helpful?