Want to ensure no duplicate values are stored in your database table columns? Certain requirements often dictate that all values for a particular column (or columns) should not be identical and remain individually unique. But how do you ensure this is handled in the database, without your required, immediate intervention? The UNIQUE constraint. In this blog post, we will see how by example…
Photo by Markus Spiske on Unsplash
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 5.7.23
Similar Blog Posts
I have blogged about the different MySQL constraints a great deal, so be sure and check out these posts:
- DEFAULT Constraint in MySQL with examples.
- Exploring MySQL Constraints: NOT NULL – With examples.
- FOREIGN KEY Referential Actions in MySQL – with examples.
- FOREIGN KEYS in MySQL with examples.
According to my interpretation of the official documentation in section 13.1.18 CREATE TABLE Syntax, the UNIQUE column definition is actually an index. When in place, it prohibits duplicate values from being stored in the target column(s).
Let’s see the table definition for the generic example used in this post. Here we are storing an id, first name, and a cell phone number.
1 2 3 4 5 6 7 8 9 | mysql> DESC cell_phone_storage; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | f_name | varchar(25) | NO | | NULL | | | phone_number | char(12) | NO | UNI | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
You can see from the table definition, column phone_number has the UNI Key assigned to it.
This data is currently stored in the table:
1 2 3 4 5 6 7 8 9 | mysql> SELECT * FROM cell_phone_storage; +----+--------+--------------+ | id | f_name | phone_number | +----+--------+--------------+ | 1 | Josh | 222-333-4444 | | 2 | Mark | 888-888-0000 | | 3 | Kim | 444-111-5555 | +----+--------+--------------+ 3 rows in set (0.00 sec) |
To fully understand what a UNIQUE constraint enforces, I will INSERT a row that has a phone_number value which already exists:
1 2 3 | mysql> INSERT INTO cell_phone_storage -> VALUES(4, 'Kerry', '888-888-0000'); ERROR 1062 (23000): Duplicate entry '888-888-0000' for key 'phone_number' |
You can see from the above error that duplicate value 888-888-0000 is not allowed since it already is present. This is what the UNIQUE constraint provides.
Let’s see how UNIQUE constraints interact with NULL values. Here we have another arbitrary table and data for demonstration purposes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> DESC messages; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | f_name | varchar(25) | YES | | NULL | | | secret_phrase | varchar(50) | YES | UNI | NULL | | +---------------+-------------+------+-----+---------+-------+ 2 rows in set (0.05 sec) mysql> SELECT * FROM messages; +--------+------------------+ | f_name | secret_phrase | +--------+------------------+ | Tom | Sweet Tomatoes | | Curly | What time is it? | +--------+------------------+ 2 rows in set (0.00 sec) |
Are two NULL values considered equivalent, and thus prohibited by the UNIQUE constraint?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> INSERT INTO messages -> VALUES('Mary', NULL), ('William', NULL); Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM messages; +---------+------------------+ | f_name | secret_phrase | +---------+------------------+ | Tom | Sweet Tomatoes | | Curly | What time is it? | | Mary | NULL | | William | NULL | +---------+------------------+ 4 rows in set (0.00 sec) |
I am sure you are already aware since it is widely known that NULL cannot be equivalent to anything. Not even another NULL. Nevertheless, I still felt it pertinent to include this information in the post. Since 2 NULL‘s can never be equivalent, the UNIQUE constraint is not violated here.
UNIQUE constraints may be defined at the table level as well. In the SHOW command below, you can see a unq_phone UNIQUE constraint, but there is something different about this one.
Notice 2 columns are part of the definition?
This is but another alternative for those critical data integrity conformation stipulations (AKA ‘business rules’) you may have to cover.
1 2 3 4 5 6 7 8 9 10 11 | mysql> SHOW CREATE TABLE cell_phone_2\G *************************** 1. row *************************** Table: cell_phone_2 Create Table: CREATE TABLE `cell_phone_2` ( `id` int(11) NOT NULL, `f_name` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL, `cell_number` char(12) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_phone` (`f_name`,`cell_number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) |
In this instance, the columns f_name and cell_number combined must be UNIQUE. Whereas in the first example, we were only concerned with the phone_number column remaining distinct.
Let’s see this functionality in action.
We have this same mock data as before, but without the rows containing NULL:
1 2 3 4 5 6 7 8 9 | mysql> SELECT * FROM cell_phone_2; +----+--------+--------------+ | id | f_name | cell_number | +----+--------+--------------+ | 1 | Josh | 222-333-4444 | | 3 | Kim | 444-111-5555 | | 2 | Mark | 888-888-0000 | +----+--------+--------------+ 3 rows in set (0.00 sec) |
See with this INSERT:
1 2 3 | mysql> INSERT INTO cell_phone_2 -> VALUES(5, 'Barry', '444-111-5555'); Query OK, 1 row affected (0.13 sec) |
No warnings or errors reported, even though the row with id 3 has that same cell_number value.
How about this one now?
1 2 3 | mysql> INSERT INTO cell_phone_2 -> VALUES(6, 'Kim', '444-111-5555'); ERROR 1062 (23000): Duplicate entry 'Kim-444-111-5555' for key 'unq_phone' |
This fails simply because the combination of ‘Kim’ and ‘444-111-5555’ already exists for row id 3 therefore, violating the UNIQUE constraint.
UNIQUE constraints are one of several available, enabling you to better maintain consistent and sound data in your database. Check them out!
Like what you have read? See anything incorrect? Please share your thoughts and comments below. Thanks for reading!!!
Explore the official MySQL 5.7 Online Manual for more information.
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.
Have I mentioned how much I love a cup of coffee?!?!
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, are 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.