Use MySQL UNIQUE Constraint in phpMyAdmin

The MySQL UNIQUE constraint is often used in a column definition in which we need each value for that column to be distinct from the others. Perhaps it is an email column for an on-line registration form and we want to ensure that users cannot register twice for an account using the same email. Whatever the case may be, UNIQUE is there to help us ensure this type of data integrity or business requirement. What if the target table already exists and you determine you need to add a UNIQUE constraint to an existing column? In this post, I will cover 2 ways you can implement a UNIQUE constraint on existing columns using the phpMyAdmin web interface…

---

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

---

What is a Unique Constraint in MySQL?

The UNIQUE constraint ensures that a column’s values remain unique or individual among all the other values in the column. In other words, no duplicates can exist in a column that is created with the UNIQUE constraint. Every value must be different in some way.

In more correct terms, UNIQUE is actually an INDEX. Below is the exact verbiage directly from the official on-line MySQL CREATE TABLE section:

“A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length.”

Table Structure

For the examples in this post, we have this ‘users’ table with 3 columns: ‘user_id’, ‘first_name’, and ‘last_name’:

Add MySQL Unique Constraint to a column: Method 1

Suppose we want to make the ‘first_name’ column UNIQUE, therefore only containing distinct values. This is not likely practical in a real-world application as many people share the same first and last names. However, for the purpose of learning and a simple example, we will go with it.

Follow these steps:

1. Click the checkbox on the far left of the column row you want to add the UNIQUE constraint to.
2. Click the Unique keyword along the With selected: section just below the column rows (indicated by the mouse pointer in the accompanying screenshot).

The below screenshot provides a visual representation of the above-described steps.

---

Add MySQL Unique Constraint to a column: Method 2

In this next example, we will assume that the ‘last_name’ column needs to be UNIQUE along with the ‘first_name’. Again, you won’t likely find this type of requirement implemented, but for our purposes here, we will.

To use method 2, follow this step (see the following screenshot for a visual representation):

1. On the column row, you want to make UNIQUE, click the More drop-down and choose (by clicking) the Unique keyword (2nd item in the list).

Upon clicking the Unique keyword in the drop-down list, this pop-up is presented, confirming the ALTER TABLE DDL command you are about to execute:

Clicking OK executes the command, carrying out the change.

Updated table definition with Unique constraints

The ‘users’ table now has this structure:

Notice both the ‘first_name’ and ‘last_name’ columns have a silver key icon for their respective rows, indicating they have the UNIQUE constraint INDEX in place.

phpMyAdmin Additional Reading

Visit these other blog posts I have written about basic phpMyAdmin usage:

• Use phpMyAdmin to change column name and datatype in MySQL
• Export MySQL data to CSV with phpMyAdmin
• Import CSV file data into MySQL table with phpMyAdmin (one of my most read articles on Medium to date!)
• MySQL CREATE TABLE in PHPMyAdmin – with examples.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

---

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.