5 Examples of a UNIQUE Constraint in a Relational Database

UNIQUE constraints help guarantee your data model integrity. This article walks you through 5 examples of UNIQUE constraints in a relational database.

Relational databases allow us to define different kinds of constraints (like UNIQUE) to implement integrity rules and ensure that data complies with them. UNIQUE constraints guarantee that no duplicate information is entered in a column (or that no set of columns has the same combination of values).

What Are Database Constraints?

Before going into detail with UNIQUE constraints, let’s explain what kind of constraints we have in relational databases and what these constraints do. We can group constraints into two main categories: one that verifies that some rules apply to data in a table (like UNIQUE or CHECK constraints) and another that verifies that integrity between tables is enforced (like foreign keys). Let’s quickly review all of them.

DEFAULT Constraints

DEFAULT constraints define a value to be used to populate a column if no value is provided at insertion. For example, you can use a function that returns the current date and time as a default for a column that stores  date and time information.

CHECK Constraints

CHECK constraints define a condition (expression) that is evaluated during inserts and updates. A CHECK constraint can reference one or more columns. An example would be that a balance is equal or greater than zero.

NOT NULL Constraints

NOT NULL constraints are defined at the column level; they do not allow NULL values in the column when the row is inserted or updated. Some database engines implement NOT NULL as CHECK constraints with the simple expression ColumnName IS NOT NULL.

UNIQUE Keys

A UNIQUE key is a constraint that guarantees that one or more columns do not have repeated values in different rows. Tables can have more than one UNIQUE key.

Primary Keys

A primary key can be considered as a combination of UNIQUE and NOT NULL constraints. The main difference is that tables can have only one primary key column; its values are both non-repeatable and do not include NULLs. Read the article On Keys to get more detailed information on the different types of keys.

Foreign Keys

Foreign keys guarantee referential integrity between tables. A foreign key is defined on table X; it references a UNIQUE or primary key on table Y. This means that the values inserted in table X must match one and only one row in table Y. To learn more about foreign keys, please read the article What Is a Foreign Key?.

If you want to learn more about the different types of constraints, more information is available in the article Database Constraints: What They Are and How to Define Them in Vertabelo.

What Is a UNIQUE Key?

As explained above, a UNIQUE constraint (also called a UNIQUE key) is a mechanism that guarantees that column values do not repeat in different rows (or, in the case of a multi-column UNIQUE key, that a combination of values does not repeat). Database engines implement UNIQUE keys using indexes, thus reducing the effort of searching for existing values when inserting or updating a row.

One table can have multiple UNIQUE keys. A table containing company vehicle information, for example, could have many UNIQUE Keys:

• A UNIQUE key composed of the country, state, and license plate number (like “US-FL-ABCD123”.
• A UNIQUE key defined on the VIN (Vehicle Identification Number), an individual value provided by each manufacturer that globally identifies each vehicle produced (like “LJCPCBLCX11000237”).
• A UNIQUE key based on the inventory number generated by the company’s accounting system (like 2345).

Depending on the requirements, all three of them could be defined as UNIQUE Keys, guaranteeing that the values entered for any vehicle are not repeated for any other vehicle.

Example #1 - How to Create a UNIQUE Key

UNIQUE keys can be created during table creation. Or you can add them later using the ALTER TABLE command, as shown below. The syntax for UNIQUE key definition may vary slightly by database engine, but in general it is:

ALTER TABLE TableName ADD CONSTRAINT UNIQUEKeyName
UNIQUE (Column [, …n] );

While creating UNIQUE constraints is not a complex task, you can save time by creating your entire data model in Vertabelo, defining all your tables and constraints, and then generating the SQL scripts to create your database structure without worrying about syntax errors.

Creating a UNIQUE key in Vertabelo is as simple as:

1. Selecting the table you want to modify and expand the Alternate (UNIQUE) Keys
2. Clicking on the Add key option on the right.
3. Entering a name for the UNIQUE key and then clicking on the down arrow to expand the key definition:

4. Choosing the column that will make up the key and select the Add Repeat with all the columns that need to be part of this constraint.

   

   Link to model (must be logged into Vertabelo to access)

   Once you’ve finished defining your table, click on the SQL Preview button to get the SQL code to create it. It will look something like this:

   Add Repeat with all the columns that need to be part of this constraint.

   -- Created by Vertabelo (http://vertabelo.com)
   -- Last modification date: 2021-12-20 18:53:57.123
   -- tables
   -- Table: Vehicle
   CREATE TABLE Vehicle (
      VehicleID int  NOT NULL,
      LicensePlate varchar(20)  NOT NULL,
      Brand varchar(50)  NOT NULL,
      Model varchar(50)  NOT NULL,
      Color varchar(50)  NOT NULL,
      Year int  NOT NULL,
      VIN varchar(20)  NOT NULL,
      PurchaseDate date  NOT NULL,
      InventoryNumber int  NOT NULL,
      CONSTRAINT UK_Vehicle_VIN UNIQUE (VIN),
      CONSTRAINT UK_Vehicle_LicensePlate UNIQUE (LicensePlate),
      CONSTRAINT UK_Vehicle_InventoryNumber UNIQUE (InventoryNumber),
      CONSTRAINT PK_Vehicle PRIMARY KEY  (VehicleID) );
   

Some Additional UNIQUE Key Examples

Now we are going to review some examples of different types of UNIQUE Keys using some Vertabelo data models as reference.

Example #2 - Single Column UNIQUE Keys

This is the simplest (and probably the most used) kind of UNIQUE constraint. This example shows a Product table where we defined a UNIQUE key on the ProductCode column, which should be populated with a unique combination of numbers and letters that identify each product:

 Link to model (must be logged into Vertabelo to access)

Syntax:

ALTER TABLE Product ADD CONSTRAINT UK_Product_ProductCode 
    UNIQUE (ProductCode);

Example #3 - Multi-Column UNIQUE Keys

Defining a UNIQUE key based on multiple columns is also a frequent option. The following example represents a very simple model for an airline’s Passenger table, where we need to register basic information and we need to uniquely identify each passenger by their legal documentation (which may vary by their country of origin). We’ll use CountryID, DocumentTypeID, and DocumentNumber columns for our UNIQUE key:

 Link to model (must be logged into Vertabelo to access)

Syntax:

ALTER TABLE Passenger ADD CONSTRAINT UK_Passenger_Document 
    UNIQUE (CountryID, DocumentTypeID, DocumentNumber);

Example #4 - Deferrable UNIQUE Keys

There are special occasions when we need a UNIQUE key that accepts duplicate values while we’re updating data. For example, if we work in the Human Resources department of a big company, we will probably have a table containing all job vacancies we need to fill and we will probably have to prioritize them. One way to be sure we focus on the most critical ones is to have a PriorityOrder column where we order the vacancies, with 1 being the most critical.

When a new vacancy appears or a position is filled, we need to “reorder” the entire list. If we have a UNIQUE key in place, that can be tricky, since we need to make the “space” available for the new vacancy being inserted before we can insert it.

Some database engines like Oracle and PostgreSQL allow constraints to be “deferred”, meaning that they will not be enforced at the moment a row is inserted or updated. Instead, the constraint is enforced when the transaction is committed, allowing UNIQUE keys to temporarily have duplicates while the transaction is open and the data is being updated.

Without deferrable constraints, inserting a row with priority 3 in a table containing 5 rows would require us to re-number the order 5 row to order 6, the order 4 row to order 5, the order 3 row to order 4, and then inserting row 3. This way, we never have a duplicate value. Having a deferrable constraint allows us to renumber all rows with an order value greater than or equal to 3 without worrying about this; the UNIQUE constraint will allow duplicate values until we commit our transaction.

Link to model (must be logged into Vertabelo to access)

Syntax:

ALTER TABLE Vacancy ADD CONSTRAINT UK_Vacancy_Priority
    UNIQUE (PriorityOrder) DEFERRABLE INITIALLY DEFERRED;

Example #5 - UNIQUE Keys and NULL Values

As we mentioned before, database engines automatically create indexes to enhance the performance when we define a UNIQUE key. That allows the database to easily find if a duplicate row exists when inserting or updating a row. But not all database engines use the same criteria when indexing NULL values, so there are some things to consider when creating UNIQUE constraints  on columns that accept NULL values.

Oracle and NULLs

To speed up index performance and reduce index size, Oracle does not index NULL values (since they do not represent any value and cannot be compared to any other value, not even another NULL). So, when we define a UNIQUE key on a column that accepts NULL values, Oracle will not generate an error if we insert multiple NULL values into it.  You can verify this with a simple test:

Link to model (must be logged into Vertabelo to access)

After creating a table with a UNIQUE constraint on a NULLable  column that accept NULL values, we issue two INSERT commands with NULLs:

INSERT INTO UNIQUENulls VALUES (1,NULL);
INSERT INTO UNIQUENulls VALUES (2,NULL);

Both rows will be inserted and no error will be raised:

Note: You can use Oracle Live SQL, a free web-based Oracle database, to test this if you do not have one installed.

SQL Server and NULLs

Even though NULL values cannot be compared, SQL Server still indexes them. If we test the same scenario described above in SQL Server, we will get a completely different result:

Link to model (must be logged into Vertabelo to access)

We can execute the same two commands in SQL Server …

INSERT INTO UNIQUENulls VALUES (1,NULL);
INSERT INTO UNIQUENulls VALUES (2,NULL);

… and we will get the following error: