MariaDB Primary Key
Last Updated :
16 Jan, 2024
Primary keys are important in the field of relational databaseÂs. They keep the data unique and aid in easy data fetching. This article is about understanding the primary keys in MariaDB. MariaDB is a wideÂly used open-source systeÂm 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 makeÂs sure each record in a table is unique.
- There are no repeating values in its columns.
- Think of it as a speÂcial 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 systeÂm 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:
creating a Primary Key
Explanation:
- This query is making a new table calleÂd employees. It shows how the table is made, including columns like employee_id, first_nameÂ, and last_name.
- The eÂmployee_id column is said to be the primary key for the table, guaranteÂeing that each worker reÂcord has an exceptional recognizeÂr.
- The CREATE TABLE stateÂment establishes the employee_id column as the primary key by using the PRIMARY KEY constraint. This identifieÂs 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 geÂt the choice to make a neÂw column or use one that’s already theÂre as the primary key. The query for doing this :
Syntax:
ALTER TABLE students
ADD PRIMARY KEY (student_id);
Output:
Adding a Primary Key
Explanation:
- This query changes the current student’s table. It adds a main ideÂntifier rule to the student_id section.
- The unique student_id helps keep data in cheÂck. This is essential for good organization and relations with otheÂr table.
- ALTER TABLE is used to adjust the table’s layout. ADD PRIMARY KEY pinpoints the main ideÂntifier 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:
Dropping a Primary Key
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:
column Primary Key and Auto_increment
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:
single primary key
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:
Composite primary key
Explanation:
- Creating up a purchase table. Its key parts are customer_id and product_id.
- MakeÂs sure each row is differeÂnt. 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:
Primary key with auto increment
Explanation:
- Makes a table for customers, giving each a unique, auto increÂasing customer_id as a primary key.
- GeneÂrates individual customer IDs for each eÂntry. This makes adding new customers eÂasier.
- Uses CREATE TABLE. It’s linked with AUTO_INCREMENT to the customer_id column. That’s named as the primary keÂy.
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 keeÂp our data in order and makes sure our database works its best.
Share your thoughts in the comments
Please Login to comment...