MariaDB Primary Key
Primary keys are important in the field of relational databases. They keep the data unique and aid in easy data fetching. This article is about understanding the primary keys in MariaDB. MariaDB is a widely used open-source system for managing relational databases.
The MariaDB database is used for various purposes such as data warehousing, e-commerce, enterprise-level features, and logging applications. MariaDB will efficiently enable you to meet all your workload. It works in any cloud database and works at any scale either small or large.
In this article, We will learn about the PRIMARY Key in MariaDB along with examples and perform various operations and queries and so on.
PRIMARY Key in MariaDB
PRIMARY key is a very important part of the database, it gives each record in a table a unique value making sure values aren’t the same. This guarantees that no two records have the same value in the column picked as the PRIMARY key.
Primary keys are really important because they keep data clean and prevent duplicates. The primary key also connects different tables using foreign keys.
It lets us link records between tables. The PRIMARY key helps keep data correct and is also important for making it quicker to get data. Searching and getting data is often fast and automatic for the primary key. The key can be one or more columns.
- A primary key makes sure each record in a table is unique.
- There are no repeating values in its columns.
- Think of it as a special tag that keeps data neat and unique.
- Can’t use foreign key without primary key.
Defining a Primary Key
When we make a table in MariaDB, we pick the column or group of columns to be the PRIMARY key. This chosen columns must have only unique values. The system helps enforce this rule, no duplicate values allowed.
Syntax:
CREATE TABLE employees
(
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
Output:
Explanation:
- This query is making a new table called employees. It shows how the table is made, including columns like employee_id, first_name, and last_name.
- The employee_id column is said to be the primary key for the table, guaranteeing that each worker record has an exceptional recognizer.
- The CREATE TABLE statement establishes the employee_id column as the primary key by using the PRIMARY KEY constraint. This identifies each record as unique.
Adding a Primary Key to a Table
To add a primary key in a table that’s already there, we use an ALTER TABLE statement. We get the choice to make a new column or use one that’s already there as the primary key. The query for doing this :
Syntax:
ALTER TABLE students
ADD PRIMARY KEY (student_id);
Output:
Explanation:
- This query changes the current student’s table. It adds a main identifier rule to the student_id section.
- The unique student_id helps keep data in check. This is essential for good organization and relations with other table.
- ALTER TABLE is used to adjust the table’s layout. ADD PRIMARY KEY pinpoints the main identifier rule.
Dropping a Primary Key From a Table
Removing a primary key from a table is needy in certain scenarios. We’ll explore the syntax and steps involved in dropping a primary key, emphasizing the careful considerations to maintain data integrity.
Syntax:
ALTER TABLE employees
DROP PRIMARY KEY;
Output:
Explanation:
- This query is used to remove the primary key constraint from the employees table.
- Important when altering table structures; dropping a primary key may be necessary for changes in database design or data manipulation.
- We used ALTER TABLE with DROP PRIMARY KEY to remove the primary key constraint from the specified table.
Primary Key and Auto_Increment Column
Understanding the relationship between primary keys and auto_increment columns is reuqired for efficient database management. We’ll discuss how an auto_increment column can be part of a primary key and its implications.
Syntax:
CREATE TABLE orders
(
order_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100),
quantity INT
);
Output:
Explanation:
- Creates a new table named orders with an auto-incrementing primary key (order_id).
- The AUTO_INCREMENT attribute ensures that each new record gets a unique identifier automatically, simplifying data insertion.
- Uses CREATE TABLE with the AUTO_INCREMENT attribute on the order_id column, designating it as the Primary key.
Multiple MariaDB Primary Key Example
Several examples will be provided to illustrate different scenarios of defining primary keys, including single-column primary keys, composite primary keys, and primary keys with auto_increment columns.
Syntax (Single-column primary key):
CREATE TABLE books
(
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(50)
);
Output:
Explanation:
- Creating a books table with a single-column primary key (book_id).
- Ensures each book in the table has a unique identifier, helping efficient data retrieval and maintenance.
- Uses CREATE TABLE with the PRIMARY KEY constraint on the book_id column.
Syntax (Composite primary key):
CREATE TABLE purchase
(
customer_id INT,
product_id INT,
purchase_date DATE,
PRIMARY KEY (customer_id, product_id)
);
Output:
Explanation:
- Creating up a purchase table. Its key parts are customer_id and product_id.
- Makes sure each row is different. It pairs up two details, the customer_id and the product_id.
- It applies a rule called PRIMARY KEY on two details (customer_id, product_id) by using CREATE TABLE.
Syntax (Primary key with auto_increment column):
CREATE TABLE customers
(
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
);
Output:
Explanation:
- Makes a table for customers, giving each a unique, auto increasing customer_id as a primary key.
- Generates individual customer IDs for each entry. This makes adding new customers easier.
- Uses CREATE TABLE. It’s linked with AUTO_INCREMENT to the customer_id column. That’s named as the primary key.
Conclusion
In this article, We have learned about the PRIMARY Key in which Primary Keys ensure each row in a table has a unique, non-null value and preventing data duplication and maintaining data integrity.
We also have seen the Add the PRIMARY key in Existing Table, Creating the PRIMARY in New Table, PRIMARY Key with Single and Composite Key. A PRIMARY keys helps keep our data in order and makes sure our database works its best.
Contact Us