MariaDB Alter Table Compound Primary Key

In this MariaDB tutorial, we will discuss what compound primary keys are, how they differ from regular primary keys, and how to add and modify compound primary keys in MariaDB. We will also provide examples to illustrate each of these concepts.

These topics are important for database administrators and developers who need to manage their database tables and maintain the integrity of their data.

Whether you are new to MariaDB or an experienced user, this information will be useful for managing your database tables and ensuring that your data is consistent and reliable.

• Working of MariaDB Alter Table Compound Primary Key
• Use of MariaDB ALTER TABLE compound primary key foreign key
• MariaDB ALTER TABLE compound primary key with a default value
• Using MariaDB alter table compound primary key auto increment

MariaDB Alter Table Compound Primary Key

A compound primary key is a type of primary key that is made up of two or more columns. A primary key is a column or set of columns that is used to uniquely identify each row in a table. It is a special type of index that enforces the uniqueness of the values in the key columns and prevents duplicate values from being inserted into the table.

A compound primary key is used when a single column is not sufficient to uniquely identify a row in a table.

For example, consider a table of customers with columns id, first_name, and last_name. If you set the id column as the primary key, it will be unique, but two customers could have the same first and last name. In this case, you could create a compound primary key using the id and last_name columns to ensure that each row in the table is uniquely identified.

Syntax of MariaDB Alter Table Compound Primary Key

To add a compound primary key to a table in MariaDB using the ALTER TABLE statement, you can use the following syntax.

ALTER TABLE `table_name` ADD PRIMARY KEY (`column_1`, `column_2`, ... `column_n`);

Where,

• ALTER TABLE: It is the command to make changes to the table.
• table_name: It is the name of the table to add the primary key.
• column_1, column_2, … column_n: These are the names of the columns that you want to include in the primary key.

Example of MariaDB Alter Table Compound Primary Key

Let’s take an example, first, let’s create a table called customers with columns id, first_name, last_name, and country.

CREATE TABLE `customers` (
  `id` INT(11) NOT NULL,
  `first_name` VARCHAR(255) NOT NULL,
  `last_name` VARCHAR(255) NOT NULL,
  `country` VARCHAR(255)
);

Next, let’s insert some data into the customers table.

INSERT INTO `customers` (`id`, `first_name`, `last_name`,`country`)
VALUES (1, 'John', 'Smith','USA'), (2, 'Jane', 'Doe','USA'), (3, 'Bob', 'Johnson','Canada');

Now you have a table called customers with columns id, first_name, and last_name, and if you want to add a compound primary key using the id and last_name columns, you can use the following ALTER TABLE statement.

ALTER TABLE `customers` ADD PRIMARY KEY (`id`, `last_name`);

The above command will add a compound primary key to the customers table using the id and last_name columns.

Check the description of the table for the compound key.

DESC customers;

Read: MariaDB See If Table Exists

MariaDB ALTER TABLE compound primary key foreign key

To add a compound primary key foreign key to a table in MariaDB, you can use the FOREIGN KEY clause of the ALTER TABLE statement.

The syntax is given below.

ALTER TABLE `table_name` ADD FOREIGN KEY (`column_1`, `column_2`, ... `column_n`)
REFERENCES `referenced_table` (`referenced_column_1`, `referenced_column_2`, ... `referenced_column_n`);

Where,

• table_name: It is the name of the table to add the foreign key.
• column_1, column_2, … column_n: These are the names of the columns that make up the foreign key.
• referenced_table: It is the name of the table that the foreign key references.
• referenced_column_1, referenced_column_2, … referenced_column_n: These are the names of the columns in the referenced table that the foreign key columns match.

Let’s take an example, we have already created the customers table in the above subsection, create the orders table with a compound primary key made up of the id and customer_id columns.

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `total` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`, `customer_id`)
);


INSERT INTO `orders` (`id`, `customer_id`, `order_date`, `total`)
VALUES (1, 1, '2022-01-01', 100.00), (2, 1, '2022-02-01', 50.00), (3, 2, '2022-03-01', 75.00);

Now, let’s add a foreign key to the orders table that references the customers table’s compound primary key.

ALTER TABLE `orders` ADD FOREIGN KEY (`customer_id`, `id`)
REFERENCES `customers` (`id`, `last_name`);

The orders table now has a foreign key that references the customers table’s compound primary key.

Read: MariaDB Order By Decreasing

MariaDB ALTER TABLE compound primary key with a default value

To add a compound primary key with a default value to a table in MariaDB, you can create a table and define the column for default then, use the ALTER TABLE statement and the ADD PRIMARY KEY keyword.

The syntax is given below.

ALTER TABLE `table_name` ADD PRIMARY KEY (`column_1`, `column_2`, ... `column_n`);

Where,

• table_name: is the name of the table to add the primary key.
• column_1, column_2, … column_n: are the names of the columns that make up the compound primary key.
• default_value: It is the default value that will be assigned to the primary key columns if a value is not specified when inserting a row into the table.

Let’s take an example by following the below steps:

Create the products table with the id, name, and category_id columns.

CREATE TABLE `products` (
  `id` int(11)  DEFAULT 0,
  `name` varchar(255) DEFAULT '',
  `category_id` int(11) NOT NULL
);

Add a compound primary key to the products table with a default value of 0 for the id column.

ALTER TABLE `products` ADD PRIMARY KEY (`id`, `category_id`);

Insert some data into the products table.

INSERT INTO `products` (`id`, `name`, `category_id`)
VALUES (1, 'Book', 1), (2, 'Laptop', 1), (3, 'Mobile', 2);

The above queries will create a products table with three rows of data. The products table now has a compound primary key made up of the id and category_id columns with a default value of 0 for the id column.

Read: MariaDB Display List of Databases

MariaDB alter table compound primary key auto increment

To add an auto-increment column to a compound primary key in a table in MariaDB, you can use the AUTO_INCREMENT keyword of the ALTER TABLE statement.

The syntax is given below.

ALTER TABLE `table_name` MODIFY `column_name` INT(11) NOT NULL AUTO_INCREMENT;

Where,

• table_name: It is the name of the table.
• column_name: It is the name of the column that you want to make an auto-increment.

Let’s take an example, you have a table called products with a compound primary key made up of the id and category_id columns, and you want to make the id column auto-increment. Here is the ALTER TABLE statement you would use.

ALTER TABLE `products` MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT;

The above command will modify the id column of the products table to be an auto-increment column. Keep in mind that in MariaDB, only one column of a compound primary key can be an auto-increment column.

Conclusion

• In conclusion, compound primary keys are a powerful tool for maintaining the integrity of your data in MariaDB.
• They allow you to define multiple columns as the primary key for a table, ensuring that each row in the table has a unique combination of values for those columns.
• By using the ALTER TABLE statement and the DEFAULT and AUTO_INCREMENT keywords, you can easily add and modify compound primary keys to meet the needs of your database.

You may also like to read the following MariaDB tutorials.

• MariaDB Set Auto Increment Value
• MariaDB Show Column Data Type
• MariaDB Alter Table Add Foreign Key
• MariaDB Add Auto Increment Column
• MariaDB Alter Table If Exists

We hope that this MariaDB tutorial has provided you with a solid understanding of how to work with compound primary keys in MariaDB. With this knowledge, you should be able to manage your database tables more effectively and ensure that your data is consistent and reliable.

• Working of MariaDB Alter Table Compound Primary Key
• Use of MariaDB ALTER TABLE compound primary key foreign key
• MariaDB ALTER TABLE compound primary key with a default value
• Using MariaDB alter table compound primary key auto increment