Unique Constraint in MariaDB

MariaDB uses relational databases similar to SQL. SQL is the precedence of MariaDB similar to the SQL mariaDB also uses some of the querying language to process through the databases. The main advantage of the MariaDB is that it runs on various operating systems and supports many programming languages like Java and Python, including PHP language.

In this article, We will understand the Unique Constraint with examples and so on.

Create Database

For performing the operations in the table in a particular database. we have to create a table in the database. we can create a database with the help of the below syntax.

CREATE DATABASE database_name ;

now we will create a database with the name details. After creating the database in the server we will create a table in the database. The above syntax will create a database in the server.
Query:

CREATE DATABASE DETAILS;

Explanation:

• In the above image, we have created a database in the MariaDB server.
• The database name is named with the name details.
• Now we can proceed with the further operations in the database.

Creating a Table with Constraints

Before getting to use the constraints while creating a table in the database we will get to know about the constraint meaning and the constraints available in the mariaDB . we will discuss about the check constraint and unique constraint in the MariaDB.

Generally constraint is nothing but a rule. In this we will discuss about the check constraint and unique constraint.

• Check Constraint : A Check Constraint in the MariaDB is a constraint that checks the value of a column in the database while inserting or updating the values in the table . While inserting the values in the table it should satisfy the boolean expression.
• Unique Constraint : A Unique Constraint in the MariaDB is a constraint that checks the value of the column if it has unique values in the given column in the database . If we try to insert a duplicate values then it will not allow to insert a duplicate value in the database.
• NOT NULL : The Not Null constraint in the MariaDB makes sure that the the specified column value in the table should have a value of specified datatype without leaving the empty cell in the table

In Database the data is stored in the form of table. A table is simply defined as set of records values along with the field names.

Syntax:

CREATE TABLE table_name
 (
    column_name data_type,
    column_name1 data_type1 [constraint_name]
    ...................................,
);

Let’s create a sample table in our database without constraints in a given database.

Creating Table Without Constraints

Query:

CREATE TABLE students
(
id INT,
name VARCHAR(17),
course VARCHAR(15),
PRIMARY KEY (id)
); 

Explanation:

• In the above image we have created a table with the name students.
• We can see that the a table with the name students is created at the left side.
• As mentioned we have not added any constraints in the given table while creating the column.
• In the above table we have id , name and course as column names.
• The id is the primary key in the above table . Similiar to the sql the primary key is unique identification for any records in the table.
• The primary key should be unique and it should not have any null values while inserting the records in the table.

Creating a Table Using Check Constraint

Query:

CREATE TABLE employee
(
id INT,
name VARCHAR(255),
age INT CHECK( age > 18 ), 
salary INT,
PRIMARY KEY (id)
);

Explanation:

• In the above image we have created a table with the name employee.
• The employee table has the the column names id ,name , age and salary.
• The id is the primary key of the table employee and every record in the table is uniquely identified with the primary key.
• The age is created with the check constraint and the value in the column age should be greater than 18 .
• Now we will insert the records in the table.

Condition 1: Let’s Insert some records for checking Check Constraints

INSERT INTO employee (id, name, age, salary) 2 VALUES
(1,"Krishna", 25, 200000),
(2, "Rama",26,100000),
(3, "Hanuman", 20, 300000),
(4, "Shiva",19,400000);

Explanation:

• In the above employee table we have successfully inserted the record values.
• We have created unique key for every record in the insert operation.
• After that we have inserted every column value satisfying the specific datatype in the table.
• We have added the age value greater than 18 in every value.

Output:

RECORDS IN THE EMPLOYEE

Condition 2: Check values during Insertion by applying the CHECK Constraints.

Age constraint failed.

Explanation:

• In the above image we have inserted records in the table employee.
• The insertion was not successfull because the records doesn’t satisfy the check constraint in the table.
• The age value is less than 18. So the record is not inserted successfully in the table.

Using the Unique Constraint

The unique constraint in the MariaDB is used to check whether the values in the column are unique or not. It should have mainly atomic values.

Creating a Table with Unique Constraint:

Condition 1: Giving unique constraint for the Single column.

Query:

CREATE TABLE staff
(
id INT,
name VARCHAR(255),
subject VARCHAR(255),
subject_id INT UNIQUE,
PRIMARY KEY (id)
);

Explanation:

• In the above image we have created a table with the name staff.
• The table staff consists of columns like id, name, subject, subject_id.
• The id is primary key of the table staff and it is unique identity for each records in the table staff.
• The subject_id in the staff is given the constraint unique. It means it should have the unique values and it should not contain the repeated values.

Condition 2: Giving Unique Constraint for Multiple Columns

Query:

CREATE TABLE STUDENTS
(
id INT,
name VARCHAR(255),
subject VARCHAR(255),
subject_id INT,
email VARCHAR(255),
UNIQUE (id, subject_id, email)
);

Explanation:

• In the above query we have created a table with the name students.
• In the students table we have created a column with the names id , name , subject and subject_id and email.
• We have made unique to the multiple columns in the above query.
• In the above query we have made the columns unique such as id , subject_id and email.
• We should insert only the unique values in the table with the specified fields .

Adding the Unique Constraint to the Table

• In this we will use the alter keyword to add the unique constraint to the table.
• First we will create a table in the database without any constraints in the database.
• Then will use the alter along with the unique constraint to add the unique constraint to the given column.
• First we will create a table without any constraint in database.

Query:

CREATE TABLE employeees
(
name VARCHAR(255),
subject VARCHAR(255),
id INT,
subject_id INT
PRIMARY KEY (id)
);

Explanation:

• In the above image we have created a table with the name employees.
• In the employees table we have created some of the columns such as id, name, subject and the subject_id.
• The id in the table employees was given as the primary key.
• Except the primary key we have not added any constraints to the above table.

Now we will add the unique constraint to any of the column that was given to the table.

we will add the unique constraint to the subject_id in the table.

Query:

ALTER TABLE employeees ADD CONSTRAINT UNIQUE (subject_id);

Explanation:

• In the above image we have used the alter keyword to add the unique constraint to the a subject_id.
• we have used the alter keyword along with add to add the constraint to the column subject_id in the table.
• Then the unique constraint will be added to the table automatically.

• With the help of the above syntax we can add the unique constraint to the table in the database.

Dropping the Unique Constraint in the Table

Query:

ALTER TABLE employeees DROP CONSTRAINT subject_id;

Explanation:

• We have given the unique constraint to the column subject_id in the table.
• With the help of the drop keyword in the MariaDB.

Conclusion

The Constraints that were created in any of the table helps us to prevent the duplication of data. The problem is that whenever the problem of data duplication occurs in the data base then processing the queries will be difficult . The constraints are also used to keep the records in the database based on the field data type. It is because whenever we want to insert the data in a proper format the constraints will restrict the insertion of data when we insert the data in the improper format.