MariaDB Alter Table Compound Primary Key

By executing a MariaDB ALTER TABLE statement, a table can be created in MariaDB by employing the compound primary key feature. This MariaDB tutorial will also cover this and we’ll discuss and reach conclusions in several circumstances to help you comprehend the subject better. Following is a list of all the subjects we’ll be covering.

  • How to add a compound primary key in the table by using the MariaDB ALTER TABLE statement?
  • How to add a compound primary key in the table for example by using the MariaDB ALTER TABLE statement?
  • How to add compound primary key foreign key in the table?
  • How to add a compound primary key with a default value in the table?

MariaDB Alter Table Compound Primary Key

Here, we will learn how to add the PRIMARY KEY column of the table by the query, which will be explained with the help of syntax and an illustrated example.

The MariaDB PRIMARY KEY is a single field or combination of fields that uniquely defines the record of the table. In a PRIMARY KEY column, it can’t contain a NULL value. And each table can contain only the PRIMARY KEY column.

In MariaDB, the ALTER TABLE statement is used to add, modify, drop/delete columns from the table. It is also used to rename columns of the table.

SYNTAX:

ALTER TABLE YOUR_TABLENAME
MODIFY COLUMN_NAME COLUMN_DEFINITION PRIMARY KEY
[ FIRST | AFTER YOUR_COLUMNNAME ]; 

In the syntax explanation:

  • YOUR_TABLENAME: It is the name of the table that is to be modified.
  • COLUMN_NAME: It is the name of the column which will be modified by using the MODIFY keyword.
  • COLUMN_DEFINITION: The datatype and definition of column i.e NULL or NOT NULL, etc.
  • [ FIRST | AFTER YOUR_COLUMNNAME: It is optional and it tells MariaDB where in the table to put its position. It depends on the user’s choice.

Here is a sample example of the MariaDB ALTER TABLE statement to add the PRIMARY KEY on the column of the table by the following query:

EXAMPLE:

ALTER TABLE STATES_OF_USA MODIFY STATE_ID INT AUTO_INCREMENT PRIMARY KEY;

DESC STATES_OF_USA;

In the aforementioned query, we have used the ALTER TABLE statement to modify the STATE_ID column with the PRIMARY KEY keyword in the column of the STATES_OF_USA table. To check new modifications done in the STATES_OF_USA table, we have used the DESC statement for a new description of the STATES_OF_USA table.

MariaDB alter table compound primary key tutorial
Example of MariaDB Alter Table Compound Primary Key

We hope that you have understood the subtopic “MariaDB Alter Table Compound Primary Key” by using the MariaDB ALTER TABLE statement on the table by the query. For a better understanding, we have used a sample example and explained it in deepness.

Read: MariaDB See If Table Exists

MariaDB Alter Table Compound Primary Key Example

Here we will learn and understand how to add a primary key column in the column of the table by using the MariaDB ALTER TABLE statement which is shown below query:

EXAMPLE:

ALTER TABLE USA_MCKESSON_COMPANY MODIFY PATIENT_ID INT AUTO_INCREMENT PRIMARY KEY;

DESC USA_MCKESSON_COMPANY;

As we can see from the query mentioned above, we modified the PATIENT_ID column in the USA_MCKESSON_COMPANY table using the ALTER TABLE statement and the keywords AUTO_INCREMENT and PRIMARY KEY. The new update to the USA_MCKESSON_COMPANY table was then described using the DESC statement.

MariaDB alter table compound primary key example
Example of MariaDB ALTER TABLE statement to add the PRIMARY KEY keyword in the column of the table.

By applying the MariaDB ALTER TABLE statement on the table returned by the query, we hope you have comprehended the subtopic “MariaDB Alter Table Compound Primary Key Example.” We have thoroughly described it using a sample example.

Read: MariaDB Order By Decreasing

MariaDB Alter Table Compound Primary Key Foreign Key

Here, we will learn and understand how to add the primary key to the foreign key of the table in the query, which will be explained with the help of a sample example.

EXAMPLE:

ALTER TABLE STATES_OF_USA DROP PRIMARY KEY, ADD PRIMARY KEY(STATE_ID,STATE_NAME);

DESC STATES_OF_USA ;

In the example of query explanation:

  • In the first query, we used the ALTER TABLE statement to drop the PRIMARY KEY from the column of the STATES_OF_USA table. Then we added the multiple PRIMARY KEY keywords to the STATE_ID and STATE_NAME columns of the STATES_OF_USA table.
  • In the end, we have used the DESC statement to describe and see new modifications done in the STATES_OF_USA table.
MariaDB alter table compound primary key foreign key example
Example of MariaDB Alter Table Compound Primary Key Foreign Key

By applying the MariaDB ALTER TABLE statement on the table returned by the query, we hope you have grasped the subtopic “MariaDB Alter Table Compound Primary Key Foreign Key.” To facilitate understanding, we provided a detailed explanation and presented a sample example.

Read: MariaDB Display List of Databases

MariaDB Alter Table Compound Primary Key With Default Value

We will learn and understand how to use the MariaDB ALTER TABLE statement with the PRIMARY KEY and DEFAULT keyword on the column of the table by the following query:

EXAMPLE:

ALTER TABLE USA_MCKESSON_COMPANY MODIFY COLUMN PATIENT_ID INT PRIMARY KEY DEFAULT 0;

DESC USA_MCKESSON_COMPANY;

In the above query, the ALTER TABLE statement is used to remove the AUTO_INCREMENT keyword and add the PRIMARY KEY keyword in the PATIENT_ID column with the DEFAULT value as 0 in the USA_MCKESSON_COMPANY table. Then we used the DESC statement to see the description of the USA_MCKESSON_COMPANY table.

Just remember that we can use the AUTO_INCREMENT and DEFAULT keywords on the same column of the USA_MCKESSON_COMPANY table.

The reason behind not using them together is that auto_increment itself defines to increment the value by 1 and the DEFAULT refers to putting value as 0 in the absence of value in the USA_MCKESSON_COMPANY table. If we try to use it together then MariaDB will throw an error.

MariaDB alter table compound primary key with default value example
Example of MariaDB Alter Table Compound Primary Key With Default Value

We hope that you have understood the subtopic “MariaDB Alter Table Compound Primary Key With Default Value” by using the MariaDB ALTER TABLE statement on the table by the query. For a better understanding, we have used a sample example and defined it in deepness.

You may also like to read the following MariaDB tutorials.

Here in this tutorial, we understood how to use the MariaDB Alter Table Compound Primary Key after reading this lesson. Moreover, we have also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

  • How to add a compound primary key in the table by using the MariaDB ALTER TABLE statement?
  • How to add a compound primary key in the table for example by using the MariaDB ALTER TABLE statement?
  • How to add compound primary key foreign key in the table?
  • How to add a compound primary key with a default value in the table?