Using MySQL Unique Constraints

MySQL CONSTRAINT is being cast off to describe the rules that permit or restrict the values in fields that may hold or limit the data that can be entered into the rows. The aim of imposing restrictions is to maintain a database’s credibility. One of them is the UNIQUE constraint.

The UNIQUE restriction guarantees that almost all elements in a field are distinct from each other. For a field or group of fields, the UNIQUE and PRIMARY KEY limits mutually have an assurance of individuality. We may, nevertheless, have several UNIQUE restraints in each table, though one PRIMARY KEY limit for each table instead. Let’s learn it by trying some examples.

Unique Constraint via Workbench:

First of all, we have to learn about how to add Unique constraints to the table while using MySQL Workbench 8.0. Open your newly installed MySQL Workbench 8.0 and connect it to the database.

In the query area, you have to write the below command to create a table ‘Person’. This table has 4 columns with one primary key. We have to specify one unique column. As you can see, we have cleared the column ‘Id’ as the ‘UNIQUE’ column:

>> CREATE TABLE Person (ID int PRIMARY KEY NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE(ID));

Now the table “Person” has been created with its “UNIQUE” column “ID”. You can find the table under the “Navigator” and “Schemas” while listed in the “Tables” option.

While inserting the records, when you tap on the “Apply” button, it will review the inserted records as shown below. You can see we have one duplicated record at lines 3, and 4 that has the same “ID”. Tap the “Apply” button to apply changes.

In the below image, it’s generating an error that the column “ID” has duplicated, which is the value “13”.

After correcting the record, when you apply the changes, it will work correctly.

Unique Constraint via Command-Line Shell:

In the MySQL command-line shell, we will be adding UNIQUE keys to one or several columns. Open your command-line first to have a glimpse of each in the below examples. Type your password to use the command shell.

Example 01: On Single Column

Within this syntax, as in column specification, we use the UNIQUE term that you’d like to apply the uniqueness law. Once we insert or alter a value that creates duplicates in the particular column, the modification will be refused by MySQL and an exception will be given as well. There will be a column restriction in this Particular limit. And you could use that to implement one field’s unique rule too. Here is a syntax for a single-column UNIQUE key:

>> CREATE TABLE table_name( col datatype UNIQUE, col datatype);

Let’s create a table “supplier” in the database “data” with three columns in it. The column “ID” is defined as “UNIQUE”.

>> CREATE TABLE data.supplier( id INT AUTO_INCREMENT NOT NULL UNIQUE, Name VARCHAR(50) NOT NULL, Area VARCHAR(50));

On checking, you can see the table has no records yet.

>> SELECT * FROM data.supplier;

Let’s insert the records into the table. The first record will be inserted into the table smoothly, as shown below.

The second record will be inserted smoothly again since it has no duplicate values at the column “ID”. On the other hand, it is taking the same value as it uses in the first query at column “Area”.

On inserting the third record, we have provided the duplicate value “1” as we have provided in the first insert statement. It will generate an error that the “ID” column is getting a duplicate value, as shown in the image below.

While checking again, you can see that the table has only the record of the first two insert statements. While there is no record from the third insert statement.

>> SELECT * FROM data.supplier;

Example 02: On Multiple Columns

With this format, after the UNIQUE term, we apply a comma-separated set of columns in brackets. The composition of values in field col1 and col2 is used by MySQL to determine the uniqueness.

>> CREATE TABLE table_name( col1 datatype, col2 datatype, UNIQUE(col1,col2));

We have been creating a table “minister” in the database “data” with five columns. The column “ID” is defined as “UNIQUE” and “PRIMARY”. The keyword “CONSTRAINT” is used to name a unique key constraint as “uc_add_sal”. The “UNIQUE” keyword is used to define a UNIQUE constraint on the columns specified in the brackets, e.g., Address and “Salary”. Now we have a total of three columns having “UNIQUE” constraint on them.

>> CREATE TABLE data.minister( Mid INT AUTO_INCREMENT PRIMARY KEY NOT NULL UNIQUE, Name VARCHAR(50) NOT NULL, Address VARCHAR(50), Job VARCHAR(50), Salary VARCHAR(50), CONSTRAINT uc_add_sal UNIQUE (Address, Salary));

On checking the table, you can see the table is empty right now.

>> SELECT * FROM data.minister;

Let’s insert some records into it. The first record will be added to the table successfully because it’s the first line and there is no row to be matched with.

Enter another unique record without any duplicate values at any column, as shown below.

It doesn’t affect when we enter the duplicate values for the columns that have no “UNIQUE” constraint on them. Have a look at the below query. It has a duplicate value at the column “Name” and “Job”. It works properly because these two columns have no “UNIQUE” constraint defined on them.

On the other hand, when we insert the duplicate value, e.g., “13” and “Rawalpindi”, it will generate an error, as shown below. This is because “13” and “Rawalpindi” have been specified earlier.

On checking, we have only three records in the table, inserted by the first three queries.

>> SELECT * FROM data.minister;

Conclusion:

We have gracefully done all the examples of defining UNIQUE constraints on the single and multiple columns while using the MySQL Workbench 8.0 and MySQL command-line client shell. Hopefully, you will get no problems while solving problems relating to UNIQUE keys.