MariaDB Unique Key [Useful Guide]

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. There are lists of the topic that comes under discussion:

• MariaDB Unique Key
• MariaDB Unique Key Null
• MariaDB Unique Key multiple Columns
• MariaDB Unique Key Drop
• MariaDB Unique Key Index
• MariaDB Add Unique Key
• MariaDB Unique Key Between
• MariaDB Unique Key Delete
• MariaDB Unique Key Exists
• MariaDB Unique Key Group By
• MariaDB Unique Key Having
• MariaDB Unique Key Join
• MariaDB Unique Key Vs Primary Key
• MariaDB Unique Key Like
• MariaDB Modify Unique Key
• MariaDB Unique Vs Index
• MariaDB Unique key Year

MariaDB Unique Key

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

We enforce a column and set a column to accept unique values then 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 is used to create and define 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 have created a table called HARVARD_UNIVERSITY. During the creation of a table, 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 by using the INSERT INTO statement. If we want to retrieve all records of the HARVARD_UNIVERSITY table, we do it using the SELECT statement.

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

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 the query. And which will be explained with the help of an illustrated example.

The MariaDB IS NULL condition is used to test NULL values by 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 gets TRUE. If the WHERE condition turns out to be FALSE then it will produce empty output columns for the result set.

We hope that you have understood how to use the MariaDB UNIQUE constraint with the IS NULL condition of the table by the query. For better understanding, we have used an example and explained it in depth.

Read: MariaDB Date Greater Than

MariaDB Unique Key multiple Columns

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

The MariaDB SELECT statement is used to retrieve 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 is used to retrieve all records of the EMAIL and STUDENT_ADMITDATE columns from the HARVARD_UNIVERSITY table. In 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 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 UNIQUE key of the table by the 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 by using the ALTER TABLE statement. And which will be explained with the help of 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 drop columns like EMAIL and PATIENT_ADMITDATE from the HARVARD_UNIVESITY table. So by using the ALTER TABLE statement, it will remove all unique key columns from the HARVARD_UNIVERISTY table.

If we want to check whether the unique key columns have been removed from the HARVARD_UNIVERSITRY table or not, 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 the query. And 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 quicker and easier. 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 is used to create an index called PATIENT_EMAIL on the EMAIL column of the HARVARD_UNIVERSITY table. We want to check whether the new index was created or not then 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 the query. For better understanding, we have used an example and explained it in depth.

Read: MariaDB Check If Rows Exists

MariaDB Add Unique Key

In this section, we will learn and understand how to use the MariaDB ALTER TABLE statement to add a unique key to the 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 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 is used to modify and add 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 or not then use the DESC statement to describe the detail of the HARVARD_UNIVERSITY table.

Read: MariaDB Insert Multiple Rows

MariaDB Unique Key Between

In this section, we will learn and understand 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 mostly 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 to 2021-08-30 values of the HARVARD_UNIVERSITY table.

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

We hope that you have understood how to use the MariaDB BETWEEN condition on the UNIQUE columns of the table by the query. For better understanding, we have used an example and explained it in depth.

Read: MariaDB Insert If Not Exists

MariaDB Unique Key Delete

In this section, we will learn and understand how to use the MariaDB DELETE statement to delete a unique key column from the table by the query. And 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 is used to delete multiple records of the EMAIL and STUDENT_ADMITDATE columns from the HARVARD_UNIVERSITY table.

If we want to check these columns from the HARVARD_UNIVERSITY table are still there or not then 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”. For better understanding, we have used an example and explained it in depth.

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 the query. And which will be explained with the help of 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. Under a SELECT, INSERT, UPDATE, or DELETE statement, it can be utilized.

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 is used to retrieve 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 the values which are 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 turns out to be 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 the query. For better understanding, we have used an example and explained it in depth.

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. For better understanding, we have used an example and explained it in depth.

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 the query. And which will be explained with the help of an illustrated example.

The MariaDB HAVING clause is used in conjunction 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 that must be included in the GROUP BY clause since they are not contained within 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. It grouped them by the STUDENT_FIRSTNAME column by using the GROUP BY clause.

The COUNT function is used to count the EMAIL column’s total numbers and check whether the value should be greater than or equal to 1 by 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. For better understanding, we have used an example and explained it in depth.

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. And which will be explained with the help of an illustrated example.

In MariaDB, JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The 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 common 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: It returns the result set where 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 which 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.
• But due to some reason, if the WHERE condition turns out to be FALSE then it will retrieve empty records of 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, 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 the primary key and the unique key of a table. For better understanding, 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 the query. And which will be explained with the help of 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 with the pattern, then it returns TRUE otherwise FALSE. The pattern is used with the LIKE operator, and this pattern is called wildcards. 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 gets TRUE. If the WHERE condition turns out FALSE then it will retrieve empty records for the output column from the HARVARD_UNIVERSITY table.

We hope that you have understood how to use the LIKE operator on the UNIQUE key column of the table by the 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]: It is optional and we can tell the MariaDB table where we want to keep the position. If we want to adjust the position of the column.

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 there are duplicate records carried by the INSERT INTO statement in the STUDENT_ENDDATE column then it will throw an [ logical | syntax] error based on the query.

if we want to check new modifications in the HARVARD_UNIVERSITY table then we will use the DESC statement for the description of the table.

We hope that you have understood the concept of the subtopic “MariaDB Modify Unique Key”. For better understanding, 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 used an example and explained it 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 and we also covered the following set of topics.

• MariaDB Unique Key
• MariaDB Unique Key Null
• MariaDB Unique Key multiple Columns
• MariaDB Unique Key Drop
• MariaDB Unique Key Index
• MariaDB Add Unique Key
• MariaDB Unique Key Between
• MariaDB Unique Key Delete
• MariaDB Unique Key Exists
• MariaDB Unique Key Group By
• MariaDB Unique Key Having
• MariaDB Unique Key Join
• MariaDB Unique Key Vs Primary Key
• MariaDB Unique Key Like
• MariaDB Modify Unique Key
• MariaDB Unique Vs Index
• MariaDB Unique key Year