How to Create and Drop a Primary Key in MySQL?

In this article, we shall be learning how in MySQL, to create and drop the primary key. One of the best practices in RDBMS involves having a primary key in the table. The consistent usage of Primary Keys is crucial whenever a database is designed for creating and enforcing Referential Integrity. Referential Integrity can be defined as the relationship between tables. It acts as the crucial requirement for maintaining proper Data Quality and flowing the standard relational database design.

Also, for every table present in a database, there must always be a primary key. When this primary key appears in other tables in the same or another database, it's known as a foreign key for this table. It is because of its relationship to data within those tables.

What is a Primary Key in MySQL?

Before we head to apply the knowledge in MySQL, drop the primary key, and let us understand what is a primary key and its importance in the table.

We can define a Primary Key as the unique ID that is distinct for each row in a table. Widely popular as PK, it can either comprise a column or a combination of columns that plays a role in providing a distinct identifier for every record in a table.

Given below are certain crucial points one must be aware of for determining the Primary Key:

• A single table consists of only one Primary Key. But this primary key could be a Composite Key, that is, a primary key having multiple columns.
• A primary key can never have a NULL value as its acts as a unique identifier for each row, which helps to distinctly define each row in the table.
• The primary key column must always have distinct values. The combination of various column values must also be unique when the Primary Key is composite.
• As MySQL works faster with integers, it is usually the Integer datatype that is considered the best choice for primary key columns as MySQL works faster with it.

While working with MySQL, the primary key is created via either the CREATE TABLE statement or an ALTER TABLE statement. Mostly the ALTER TABLE statement is often used to drop, disable or enable a primary key in MySQL.

Create Primary Key - Using CREATE TABLE statement

In this section, we will be learning in MySQL to drop the primary key via the CREATE TABLE statement. We first start by understanding the syntax and parameters needed to achieve MySQL drop primary key.

Syntax
Below is the syntax for creating the primary key in MySQL via the CREATE TABLE statement:

The code language implemented is the Structured Query Language, widely popular as SQL.

Parameters:

• table_name: This is the name of the table that you want to create.
• column1, column2, ... column_n These are the column that will represent the primary key while you create the table.
• constraint_name: This is the name given to the primary key.
• column1, column2: These are columns that you want to create in the table. This includes the column name that would be the primary key too.

Example
Now we will be covering the example of how one can create a Primary Key via the CREATE TABLE statement.

Let us create a table named "School_One," with one column representing the Primary key and another table, "School_Two," where more than one column shall make the composite key its primary key.

Code:

Explanation:
As seen above, we have showcased two examples to create Primary Key via the CREATE TABLE statement. In the first table, named School_One, the primary key is called Class_ID_pk. It is created with only one column - the Class_ID column. While in the second table, named School_Two, the primary key is called Class_ID_pk. It is created with more than one column - the Class_ID and Class columns. Here, each combination of Class_ID and Class columns must be distinct in the School_Two table.

Create Primary Key - Using ALTER TABLE statement

In this section, we will be learning in MySQL to drop the primary key via the ALTER TABLE statement. We first start by understanding the syntax and parameters needed to achieve MySQL drop primary key.

Syntax
Below is the syntax for creating the primary key in MySQL via the ALTER TABLE statement:

The code language implemented is the Structured Query Language, widely popular as SQL.

Parameters:

• table_name: This is the name of the table that you want to modify.
• column1, column2, ... column_n These are the column that will represent the primary key while you create the table.
• constraint_name: This is the name given to the primary key.

Example
Now we will be covering the example of how one can two examples create a Primary Key via the ALTER TABLE statement.

Let us create a table named "School_One," with one column representing the Primary key and another table, "School_Two," where more than one column shall make the composite key its primary key.

Code:

Explanation:
As seen above, we have showcased two examples to create Primary Key via the ALTER TABLE statement. In the first table, named School_One, we created the primary key called Class_ID_pk over the existing School_One table. The Primary key is created with only one column - the Class_ID column. While in the second table named School_Two, the primary key is called Class_ID_pk over the existing School_Two table. It is created with more than one column - the Class_ID and Class columns. Here, each combination of Class_ID and Class columns must be distinct in the School_Two table.

Drop Primary Key

Now that we have learned how to create the primary key using both the CREATE TABLE statement and the ALTER TABLE statement. In this section of the article, we shall be highlighting how one in MySQL drops the primary key.

Syntax
Below is the syntax for MySQL to drop the primary key via the ALTER statement:

Parameters:

• table_name: This is the name of the table that you want to modify.

Example
Now we will be covering the example of how one in mysql drops the primary key.

Code:

Explanation:
As seen above, we have showcased two examples in the MySQL drop primary key. We used the ALTER statement to drop the primary key for both the tables, School_One and School_Two. As we know that one table can have only one primary key created with either one column in mind or many columns combined like a composite key. Hence, we do not need to define the name of the primary key for either of them.