MariaDB Unique Key

In this MariaDB tutorial, we will discuss how to create a MariaDB UNIQUE Key and look at several examples related to its use in different places.

MariaDB Unique Key

In this section, we will learn and understand how to use the MariaDB UNIQUE key in the table by query, which will be explained with an illustrated example.

If we enforce a column and set it to accept unique values, we will use the UNIQUE constraint in MariaDB. A UNIQUE constraint is a consistency constraint that guarantees the uniqueness of values in a column or set of columns.

The MariaDB CREATE TABLE statement creates and defines a table with the UNIQUE key constraint. Here is an illustrated example of the MariaDB UNIQUE key in the creation of a table by the following query:

EXAMPLE:

create table HARVARD_UNIVERSITY (
	STUDENT_ID INT,
	STUDENT_FIRSTNAME VARCHAR(50),
	STUDENT_LASTNAME VARCHAR(50),
	EMAIL VARCHAR(50) UNIQUE,
	GENDER VARCHAR(50),
	STUDENT_ADMITDATE DATE UNIQUE,
	STUDENT_ENDDATE DATE
);

INSERT INTO HARVARD_UNIVERSITY (STUDENT_ID, STUDENT_FIRSTNAME, STUDENT_LASTNAME, EMAIL, GENDER, STUDENT_ADMITDATE, STUDENT_ENDDATE)
 VALUES (1, 'Elonore', 'Rosenschein', 'erosenschein0@ucoz.com', 'Genderfluid', '2021-07-11 09:30:03', '2022-09-29 07:07:32'),
(2, 'Algernon', 'Joblin', 'ajoblin1@house.gov', 'Male', '2021-07-22 10:14:31', '2024-01-18 20:00:28'),
(3, 'Dieter', 'Serginson', 'dserginson2@wired.com', 'Male', '2021-08-27 08:38:04', '2022-11-24 16:36:51'),
(4, 'Buddy', 'Zavittieri', 'bzavittieri3@paginegialle.it', 'Male', '2021-08-30 19:13:15', '2022-03-31 03:37:28'),
(5, 'Todd', 'Bullan', 'tbullan4@amazon.de', 'Male', '2021-07-05 20:23:22', '2023-04-13 16:39:03'),
(6, 'Coletta', 'Dabbs', 'cdabbs5@hubpages.com', 'Non-binary', '2021-07-25 08:58:38', '2025-03-12 09:35:11'),
(7, 'Jacinda', 'Matzkaitis', 'jmatzkaitis6@wikipedia.org', 'Female', '2021-08-02 08:53:09', '2022-09-08 22:31:19'),
(8, 'Gerek', 'Greim', 'ggreim7@uol.com.br', 'Non-binary', null, NULL),
(9, 'Anthiathia', 'Huggins', 'ahuggins8@storify.com', 'Female', null, NULL),
(10, 'Keefer', 'Westcott', 'kwestcott9@jalbum.net', 'Male', '2021-08-12 22:34:07', '2024-03-23 16:52:10');

SELECT * FROM HARVARD_UNIVERSITY;

In this preceding query, we created a table called HARVARD_UNIVERSITY. We used the unique constraint for the EMAIL and STUDENT_ADMITDATE columns of the HARVARD_UNIVERSITY table.

We have inserted some records into the HARVARD_UNIVERSITY table using the INSERT INTO statement. If we want to retrieve all records of the HARVARD_UNIVERSITY table, we use the SELECT statement.

We hope you have understood how to use the MariaDB UNIQUE key in detail. We have used an example and explained it in depth for a better experience.

Read: MariaDB Select Unique

MariaDB Unique Key Null

Here, we will learn and understand how to use the MariaDB IS NULL condition on the UNIQUE column of the table by query, which will be explained with an illustrated example.

The MariaDB IS NULL condition tests NULL values in the SELECT statement. It is also used with the UPDATE, DELETE, and INSERT statements.

Let’s see an illustrated example of the MariaDB IS NULL condition on the UNIQUE column of the table by the following query:

EXAMPLE:

SELECT STUDENT_FIRSTNAME, STUDENT_LASTNAME FROM HARVARD_UNIVERSITY 
WHERE STUDENT_ADMITDATE IS NULL 
AND STUDENT_ENDDATE IS NULL;

In the preceding query, we have used the SELECT statement to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table. In the WHERE condition, the STUDENT_ADMITDATE and STUDENT_ENDDATE columns are used with the IS NULL condition to find null values in the UNIQUE columns of the HARVARD_UNIVERSITY table.

The SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table if the WHERE condition is TRUE. If the WHERE condition is FALSE, it will produce empty output columns for the result set.

We hope you understand how to use the MariaDB UNIQUE constraint with the IS NULL condition of the table by the query. We have used an example and explained it in depth to help you better understand.

Read: MariaDB Date Greater Than

MariaDB Unique Key multiple Columns

We will learn and understand how to use the SELECT statement to select multiple columns of the UNIQUE key column of the table, which will be explained with an illustrated example.

The MariaDB SELECT statement retrieves all records from one or more tables. Here is an example of MariaDB Unique Key Multiple Columns from the table by the following query:

EXAMPLE:

SELECT EMAIL,STUDENT_ADMITDATE FROM HARVARD_UNIVERSITY;

In this preceding query, the SELECT statement retrieves all records of the EMAIL and STUDENT_ADMITDATE columns from the HARVARD_UNIVERSITY table. The EMAIL and STUDENT_ADMITDATE columns both carry the UNIQUE keys.

This means that the EMAIL and STUDENT_ADMITDATE columns have unique records in the HARVARD_UNIVERSITY table. As for the detail, we try to use the INSERT statement to insert new records with Xerox, and then it will throw an error for the query.

We hope that you have understood how to use the MariaDB SELECT statement on the multiple columns of the table’s UNIQUE key by query. We have used an example and explained it in detail for better understanding.

Read: MariaDB Check Empty String

MariaDB Unique Key Drop

In this section, we will learn and understand how to drop unique key columns using the ALTER TABLE statement, which will be explained with an illustrated example.

The MariaDB ALTER TABLE statement is used to drop/ delete columns from the table. Here is an illustrated example of the MariaDB ALTER TABLE statement to drop the UNIQUE key column from the table by the following query:

EXAMPLE:

ALTER TABLE HARVARD_UNIVERSITY 
DROP EMAIL, PATIENT_ADMITDATE;

DESC HARVARD_UNIVERSITY;

As we see in the above query, the ALTER TABLE statement is used to remove columns like EMAIL and PATIENT_ADMITDATE from the HARVARD_UNIVERSITY table. Thus, the ALTER TABLE statement will remove all unique key columns from the HARVARD_UNIVERSITY table.

If we want to check whether the unique key columns have been removed from the HARVARD_UNIVERSITRY table, we can use the DESC statement for the description.

Read: MariaDB Check String Length

MariaDB Unique Key Index

In this section, we will learn and understand how to create an index for the unique key column of the table by query, which will be explained with the help of an illustrated example.

The MariaDB database index is a type of book index from which we will find information quickly and easily. To create an index, we use the CREATE INDEX statement.

SYNTAX:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_LIST);

The syntax explanation:

• First, specify the index_name in the CREATE INDEX clause.
• Second, specify the table_name and a list of comma-separated column_name in the ON clause. The index must include the column list that we must define in the parenthesis.

Here is an illustrated example of the MariaDB CREATE INDEX statement for the unique key column of the table by the following query:

EXAMPLE:

CREATE INDEX PATIENT_EMAIL 
ON HARVARD_UNIVERSITY( EMAIL); 

EXPLAIN SELECT * FROM HARVARD_UNIVERSITY;

In this preceding query, the CREATE INDEX statement creates an index called PATIENT_EMAIL on the EMAIL column of the HARVARD_UNIVERSITY table. To check whether the new index was created, we will use the EXPLAIN SELECT statement.

We hope that you have understood how to create an index for the UNIQUE key column of the table by query. We have used an example and explained it in depth to help you better understand.

Read: MariaDB Check If Rows Exists

MariaDB Add Unique Key

In this section, we will learn how to use the MariaDB ALTER TABLE statement to add a unique key to the table column by the query. Which will be explained with the help of an illustrated example.

The MariaDB ALTER TABLE statement is used to add, modify, and drop/ delete columns from the table. It is also used to rename columns of the table. Here is an illustrated example of the MariaDB Add Unique Key of the table by the following query:

EXAMPLE:

ALTER TABLE HARVARD_UNIVERSITY ADD STUDENT_ENDDDATE DATETIME UNIQUE;

DESC HARVARD_UNIVERSITY;

In the preceding query, the ALTER TABLE statement modifies and adds the UNIQUE key in the STUDENT_ENDDATE column of the HARVARD_UNIVERSITY table.

If we want to check whether the UNIQUE key is added to the HARVARD_UNIVERSITY table, we can use the DESC statement to describe its details.

Read: MariaDB Insert Multiple Rows

MariaDB Unique Key Between

In this section, we will learn how to use the MariaDB UNIQUE key with the BETWEEN condition on the table by the query. And we have used an example and explained it in detail.

In a SELECT statement, the MariaDB BETWEEN condition is mainly used for values inside a zone. It’s also utilized in statements like INSERT, UPDATE, and DELETE. Here is an illustrated example of the MariaDB BETWEEN condition on the UNIQUE key column of the table by the following query:

EXAMPLE:

SELECT STUDENT_FIRSTNAME, STUDENT_LASTNAME FROM HARVARD_UNIVERSITY 
WHERE STUDENT_ADMITDATE BETWEEN '2021-07-05 ' AND '2021-08-30 ';

As we see in the above query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

In the WHERE condition, the STUDENT_ADMITDATE column is used with the BETWEEN condition to find the values between 2021-07-05 and 2021-08-30 in the HARVARD_UNIVERSITY table.

The SELECT statement will retrieve all records of the HARVARD_UNIVERSITY table if the WHERE condition gets TRUE.

We hope you understand how to use the MariaDB BETWEEN condition on the UNIQUE columns of the table by query. We have used an example and explained it in depth to help you understand.

Read: MariaDB Insert If Not Exists

MariaDB Unique Key Delete

In this section, we will learn how to use the MariaDB DELETE statement to delete a unique key column from the table by the query. Which will be explained with the help of an illustrated example.

The MariaDB DELETE statement is used to delete one or more records from the table by the query. Here is an illustrated example of a MariaDB DELETE statement to delete multiple records of unique keys from the table by the following query:

EXAMPLE:

DELETE  EMAIL, STUDENT_ADMITDATE FROM HARVARD_UNIVERSITY;

DESC HARVARD_UNIVERSITY;

As we see in the above query, the DELETE statement deletes multiple records of the EMAIL and STUDENT_ADMITDATE columns from the HARVARD_UNIVERSITY table.

If we want to check whether these columns from the HARVARD_UNIVERSITY table are still there, we will use the DESC statement to describe the details of the HARVARD_UNIVERSITY table.

We hope that you have understood the subtopic “MariaDB Unique Key Delete.” We have used an example and explained it in depth to help you understand.

Read: MariaDB Median

MariaDB Unique Key Exists

We will learn and understand how to use the MariaDB EXISTS condition on the UNIQUE key column of the table by query, which will be explained with an illustrated example.

When the MariaDB EXISTS condition is used with a subquery, it is said to be “met” if the inner select statement returns at least one record. It can be utilized under a SELECT, INSERT, UPDATE, or DELETE statement.

Here’s an illustrated example of the MariaDB EXISTS condition on the UNIQUE key column of the table by the following query:

EXAMPLE:

SELECT EMAIL,STUDENT_ADMITDATE FROM HARVARD_UNIVERSITY
WHERE EXISTS(SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=5);

In the OUTER QUERY, the SELECT statement is used to retrieve all records of the EMAIL and STUDENT_ADMITDATE columns from the HARVARD_UNIVERSITY table based on the EXISTS condition.

In the INNER QUERY, the SELECT statement retrieves all records from the HARVARD_UNIVERSITY column based on the WHERE condition.

In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL operator to find values greater than or equal to 5 in the HARVARD_UNIVERSITY column.

The SELECT statement will retrieve all records from the HARVARD_UNIVERSITY column only when the WHERE condition is TRUE.

Once the INNER QUERY turns out to be TRUE then the OUTER QUERY will retrieve all unique key columns from the HARVARD_UNIVERSITY table.

We hope that you have understood how to use the MariaDB EXISTS condition on the UNIQUE key column of the table by query. We have used an example and explained it in depth to help you better understand.

Read: MariaDB Drop Index

MariaDB Unique Key Group By

In this section, we will learn and understand how to use the MariaDB GROUP BY clause with the UNIQUE key column of the table by the query. And which will be explained with the help of an illustrated example.

The group by clause in MariaDB divides a result’s rows into categories. The aggregate functions count(), min(), max(), sum(), and avg() are frequently used with the group by function to obtain attributes of groups.

For example, the number of elements (count), the total of values (sum), the maximum element (max), the minimum element (min), and the average of elements (avg).

Here is a sample example of the MariaDB GROUP BY clause on the UNIQUE key constraint of the table by the following query:

EXAMPLE:

SELECT STUDENT_FIRSTNAME, STUDENT_LASTNAME FROM HARVARD_UNIVERSITY 
 GROUP BY STUDENT_ADMITDATE;

As we see in the above query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

At the end of the query, we have grouped the STUDENT_ADMITDATE column of the HARVARD_UNIVERSITY table by using the GROUP BY clause in the SELECT statement.

We hope you have understood the “MariaDB Unique Key Group By” subtopic. We have used an example and explained it in depth to help you understand.

Read: MariaDB Delete From Statement

MariaDB Unique Key Having

In this section, we will learn and understand how to use the MariaDB HAVING clause on the UNIQUE key column of the table by query, which will be explained with the help of an illustrated example.

The MariaDB HAVING clause is used with the GROUP BY clause to limit the returned rows to those that meet the condition. The syntax of the HAVING clause on the UNIQUE key column of the table by the below query:

SELECT EXPRESSION, AGGREGATE_FUNCTION(EXPRESSION),
FROM TABLE_NAME
WHERE [CONDITIONS]
GROUP BY COLUMN_NAME
HAVING EXPRESSION;

The syntax explanation:

• EXPRESSION: The expressions must be included in the GROUP BY clause since they are not in an aggregate function.
• AGGREGATE_FUNCTION: It could be a function like the SUM, COUNT, MIN, MAX, or AVG.
• table_name: The table from which we need to retrieve records. There should be at least one table in the FROM clause.
• WHERE [condition]: The condition should be met for the records to be selected.
• HAVING condition: This is an additional condition that is only applied to aggregated results to limit the groupings of returned rows. The result set will only include those groups whose condition evaluates to TRUE.

Let’s see an illustrated example of the MariaDB HAVING clause on the UNIQUE key column of the table by the following query:

EXAMPLE:

SELECT EMAIL, STUDENT_ADMITDATE FROM HARVARD_UNIVERSITY
GROUP BY STUDENT_FIRSTNAME
HAVING COUNT(EMAIL) >=1;

As we see in the above query, the SELECT statement is used to retrieve all records of the EMAIL and STUDENT_ADMITDATE columns from the HARVARD_UNIVERSITY table. Then, using the GROUP BY clause, it groups them by the STUDENT_FIRSTNAME column.

The COUNT function counts the EMAIL column’s total numbers and checks whether the value should be greater than or equal to 1 using the HAVING clause.

We hope that you have understood how to use the MariaDB HAVING clause on the UNIQUE key column of the table by the query. We have used an example and explained it in depth to help you better understand.

Read: MariaDB GROUP BY

MariaDB Unique Key Join

In this section, we will learn and understand how to use the MariaDB JOIN clause on the UNIQUE key column for the table by query, which will be explained with an illustrated example.

In MariaDB, JOIN is used to merge rows from more than one table based on common columns in the tables. In other words, data is extracted from more than one table into a single table using the JOIN clause.

• The JOIN clause can be used when there are two or more two tables with standard columns.

There are four types of JOIN in MariaDB:

• INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
• LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• CROSS JOIN returns the result set in which each row in a table is joined with each row in another table.

Here is an illustrated example of the MariaDB JOIN clause on the UNIQUE key column of the table by the following query:

EXAMPLE:

SELECT MIT_STUDENTS.FIRST_NAME, MIT_STUDENTS.PHYSICS, HARVARD_UNIVERSITY.STUDENT_FIRSTNAME,
HARVARD_UNIVERSITY.STUDENT_ADMITDATE
FROM MIT_STUDENTS
INNER JOIN HARVARD_UNIVERSITY
ON MIT_STUDENTS.ID=HARVARD_UNIVERSITY.STUDENT_ID
WHERE MIT_STUDENTS.ID >=5;

• As we see in the above query, the SELECT statement is used to retrieve all records of the FIRST_NAME, PHYSICS, STUDENT_FIRSTNAME, and STUDENT_ADMITDATE columns from both tables i.e; MIT_STUDENTS and HARVARD_UNIVERSITY.
• Both tables MIT_STUDENTS and HARVARD_UNIVERSITY are joined on one common column i.e; ID and STUDENT_ID by using the ON condition of the INNER JOIN clause.
• In the WHERE condition, the ID column of the MIT_STUDENTS table is used with the GREATER THAN or EQUAL operator to find the value that is greater than or equal to 5.
• If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the MIT_STUDENTS and HARVARD_UNIVERISTY tables.
• However, if the WHERE condition turns out to be FALSE, it will retrieve empty records from both tables for the output column.

We hope that you have understood the subtopic “MariaDB Unique Key Join”. We have used an example and explained it in-depth, for better understanding.

Read: MariaDB Max Connections – Helpful Guide

MariaDB Unique Key Vs Primary Key

We will learn and understand how to see the difference between the MariaDB UNIQUE key and PRIMARY key:

PRIMARY KEY:

The MariaDB PRIMARY KEY column is used to identify the unique row in the table. We need to add the integrity constraints of the table. And there can be only one primary key allowed in the table. It can’t carry duplicate or NULL values in the table. It can also be used as the FOREIGN KEY column of the other table.

Let’s see an example below:

We have a table called HARVARD_UNIVERSITY which stores student records and it is one of the top 5 colleges in the USA (United States of America). We have used the DESC statement to describe the columns of the HARVARD_UNIVERSITY table by the following query:

EXAMPLE:

DESC HARVARD_UNIVERSITY;

STUDENT_ID is a primary key column of the table. So, the ID of each employee is unique and two students can’t carry duplicate STUDENT_ID in the HARVARD_UNIVERSITY table.

UNIQUE KEY:

The MariaDB UNIQUE key is used to uniquely identify each tuple of the table.

It can carry multiple unique keys on the table. The NULL value is allowed in the UNIQUE key column of the table. It can also be used as a FOREIGN KEY in another table.

Let’s have a look at the example,

We have a table called HARVARD_UNIVERSITY which stores student records and it is one of the top 5 colleges in the USA (United States of America). We have used the DESC statement to describe the columns of the HARVARD_UNIVERSITY table by the following query:

EXAMPLE:

DESC HARVARD_UNIVERSITY;

The EMAIL, STUDENT_ADMITDATE, and STUDENT_ENDDATE columns carry the unique key column in the HARVARD_UNIVERSITY table. Like the email, the admit date and end date can have unique values, and some students might have NULL values, which are acceptable for the UNIQUE key.

The difference between the PRIMARY KEY and UNIQUE KEY is given below:

PRIMARY KEY	UNIQUE KEY
It has unique identifier rows in the table.	It has unique identifier rows of a table if there is no primary key.
It can’t be NULL.	It can be NULL.
It can carry one primary key on the table.	It can carry multiple unique keys on the table.
When you use Selection as the main key, you get a principal theme.	Selection with a unique key generates a non-clustered index.

We hope that you have understood the difference between a table’s primary key and unique key. To help you do so, we have used an example and explained it in depth.

Read: What is MariaDB Sharding

MariaDB Unique Key Like

Here, we will learn and understand how to use the MariaDB LIKE operator on the UNIQUE key column of the table by query, which will be explained with an illustrated example.

The LIKE operator is a logical operator in MariaDB that returns TRUE when any string matches the specific pattern provided with the LIKE operator. In other words, with the help of a LIKE operator, we can test whether the string or expression matches the pattern.

If the string or expression matches the pattern, it returns TRUE; otherwise, it returns FALSE. The pattern is used with the LIKE operator, and it is called a wildcard. Two wildcards are used with the like operator in MariaDB.

• Percent (%): It is called a percent wildcard that matches any string with any number of characters.
• Underscore(_): It is called an underscore wildcard that matches any single character.

SELECT STUDENT_FIRSTNAME, STUDENT_LASTNAME,STUDENT_ADMITDATE FROM HARVARD_UNIVERSITY
WHERE EMAIL LIKE '%m';

As we see in the above query, the SELECT statement retrieves all records of the STUDENT_FIRSTNAME, STUDENT_LASTNAME, and EMAIL columns from the HARVARD_UNIVERSITY table. In the WHERE condition, the EMAIL column is used with the LIKE operator to find a string value whose name ends with the alphabet m of the HARVARD_UNIVERSITY table.

The SELECT statement will retrieve all records of the HARVARD_UNIVERSITY table if the WHERE condition is TRUE. If the WHERE condition is FALSE, it will retrieve empty records for the output column.

We hope you understand how to use the LIKE operator on the UNIQUE key column of the table by query. For better understanding, we have used an example and explained it in detail.

Read: MariaDB Rename View [Complete tutorial]

MariaDB Modify Unique Key

In this section, we will learn and understand how to use the MariaDB ALTER TABLE statement to modify the UNIQUE key column of the table by the query. And which will be explained with the help of an illustrated example.

The MariaDB ALTER TABLE statement is used to modify the column of the table. Here is the syntax of modifying the UNIQUE column of the table by using the ALTER TABLE statement by the following query:

SYNTAX:

ALTER TABLE YOUR_TABLE_NAME 
MODIFY YOUR_COLUMN_NAME DATATYPE  YOUR_COLUMN_DEFINITION
[AFTER | FIRST YOUR_COLUMN_NAME];

The syntax explanation:

• YOUR_TABLE_NAME: The name of the table that we want to modify.
• YOUR_COLUMN_NAME: The name of the column that we want to modify.
• COLUMN_DEFINITION: The column can be defined as a NULL or NOT NULL type.
• [AFTER | FIRST YOUR_COLUMN_NAME]: This is optional. We can tell the MariaDB table where we want to keep the column’s position or adjust it.

EXAMPLE:

ALTER TABLE HARVARD_UNIVERSITY 
MODIFY STUDENT_ENDDDATE DATETIME UNIQUE;

DESC  HARVARD_UNIVERSITY;

In this preceding query, the ALTER TABLE statement is used to modify the STUDENT_ENDDATE column with the UNIQUE key and DATETIME datatype. This means that by modification the STUDENT_ENDDATE column will only carry unique records in the HARVARD_UNIVERSITY table.

If the INSERT INTO statement carries duplicate records in the STUDENT_ENDDATE column, it will throw an [ logical | syntax] error based on the query.

If we want to check new modifications in the HARVARD_UNIVERSITY table, we will use the DESC statement to describe the table.

We hope that you have understood the concept of the subtopic “MariaDB Modify Unique Key.” To help you understand, we have used an illustration and exemplified it in depth.

Read: MariaDB Date_Format + 9 Useful Examples

MariaDB Unique Vs Index

In this section, we will learn about the other index i.e; PLAIN INDEX and FULL-TEXT INDEX in detail.

As the description of the UNIQUE index says that the modifier directs the variety of values in the indexes must be exceptional. In MariaDB, the KEY and INDEX both are interchangeable and both statements should work with either keyword. Let’s see the description of the PLAIN INDEX and FULL-TEXT INDEX:

PLAIN INDEX:

The MariaDB PLAIN index doesn’t necessarily have to be unique.

FULL-TEXT INDEX:

The MariaDB FULL-TEXT index is used for full-text searching and indexing. It allows more options when we are looking for a small portion of text from a field. It can be used in ISAM, Aria, InnoDB, and Mroonga tables and can be created with VARCHAR, CHAR, and TEXT columns. If we have a partitioned table then it will not contain full-text indexes even if the storage engine supports them.

When we create a table, we can give a full-text index definition and we can also give its definition by using the ALTER TABLE statement or CREATE INDEX statement.

We hope that you have understood how to use the MariaDB UNIQUE or INDEX key for the table. For better understanding, we have explained it in depth.

Read: MariaDB Rename Table

MariaDB Unique key Year

Here we will learn and understand how to use the MariaDB YEAR function on the UNIQUE key column of the table by the following query:

The MariaDB YEAR function is used to extract the year portion value from the expression or column_name of the table by the query. Here is an illustrated example of the MariaDB YEAR function on the UNIQUE key column of the table by the following query:

EXAMPLE:

SELECT STUDENT_FIRSTNAME, STUDENT_LASTNAME,YEAR(STUDENT_ADMITDATE), 
YEAR(STUDENT_ENDDATE) FROM HARVARD_UNIVERSITY;

As we see in the above query, the SELECT statement is used to retrieve all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

The MariaDB YEAR function is used to extract the year portion value from the STUDENT_ADMITDATE and STUDENT_ENDDATE columns in YYYY format from the HARVARD_UNIVERSITY table.

The SELECT statement will work only when it retrieves all records of the column and the function works properly in the HARVARD_UNIVERSITY table. Otherwise, the statement will give a logical or syntax error of the above query.

We hope that the subtopic concept “MariaDB Unique Key Year” may have been understood by you properly. For better knowledge, we have explained an example in depth.

Also, take a look at some more MariaDB tutorials.

• MariaDB Drop Table + Examples
• MariaDB If statement in Select
• MariaDB Logs – Helpful Guide
• MariaDB Temporary Table + Examples
• MariaDB Reset Root Password

So, in this tutorial, we have learned how to create and use the UNIQUE key in MariaDB.