How to Define a UNIQUE Key in Vertabelo

Among the many database constraints available to us, the UNIQUE key constraint ensures the uniqueness of data in a column or a set of columns. Read on to find out more about the UNIQUE key constraint and how to define it in Vertabelo.

In this article, we’ll focus on the UNIQUE key constraint. We’ll start with its basic definition and usage and gradually build up to more advanced options. Also, we’ll jump into Vertabelo and create an ER diagram that uses the UNIQUE key constraint. You’ll see that the UNIQUE key offers different options in various database engines.

Let’s get started.

What Is a UNIQUE Key Constraint?

The UNIQUE key is one of the database constraints that allow you to set rules for your data. It prevents the column from storing duplicate values.

You can learn about  the other database constraints in this article.

UNIQUE Key on a Single Column

Let’s look at the various ways we can define the UNIQUE key constraint on a single column.

1.      During table creation

The UNIQUE key constraint can be defined with the column definitions, like this …

CREATE TABLE Persons (
Id int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
SSN varchar(9) UNIQUE);

… or after all the columns are defined, like this:

CREATE TABLE Persons (
Id int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
SSN varchar(9),
CONSTRAINT unique_ssn UNIQUE(SSN));

2.      After table creation

Sometimes, we decide to make a column unique after table creation. Here’s how we do it:

CREATE TABLE Persons (
Id int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
SSN varchar(9));
ALTER TABLE Persons ADD UNIQUE(SSN);

We use the ALTER TABLE statement to add or remove database constraints.

Let’s Test It

The constraint implemented on the SSN column is ready to be tested.

First, we insert some data into our Persons table:

	INSERT INTO Persons VALUES(1, 'David', 'Anderson', '123123123');

But what if we insert another data row with the same SSN value?

	INSERT INTO Persons VALUES(2, 'Anne', 'Johns', '123123123');

This results in an error:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "unique_ssn"
  Detail: Key (ssn)=(123123123) already exists. 

The UNIQUE key constraint works as expected!

UNIQUE Key on Multiple Columns

Let’s see how to define the UNIQUE key on a set of columns:

CREATE TABLE Persons (
Id int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
SSN varchar(9),
CONSTRAINT unique_name UNIQUE(FirstName, LastName)); 

Or, after table creation:

ALTER TABLE Persons ADD UNIQUE(FirstName, LastName);

Please note that in this case, only the combination of columns must be unique, but not each column individually. So, the following INSERT statements are all valid.

INSERT INTO Persons VALUES(1, 'David', 'Anderson', '123123123');
INSERT INTO Persons VALUES(2, 'Anne', 'Johns', '123123123');
INSERT INTO Persons VALUES(3, 'David', 'Johns', '123123123');
INSERT INTO Persons VALUES(4, 'Anne', 'Anderson', '123123123'); 

The UNIQUE key constraint implemented on a set of columns doesn’t let duplicate value groups sneak in, just like the one implemented on a single column doesn’t let duplicate values in.

UNIQUE Key as a Candidate Key

A candidate key is a column or a set of columns that identify each row uniquely. So, the UNIQUE key qualifies to be the candidate key. Check out this article to learn more about database keys.

Have you heard about the foreign key constraint? It lets you link data stored in different tables. Get the basics on foreign keys in this article and then see how to implement foreign keys in a physical model here!

How to Define a UNIQUE Key Constraint in Vertabelo

In this section, you’ll learn how to define the UNIQUE key in Vertabelo. Also, we’ll take a look at some of the more advanced options.

Basic Definition of a UNIQUE Key in Vertabelo

Let’s define the UNIQUE key constraint in Vertabelo. To do so, create a table and navigate to the Alternate (unique) keys section in the right-side panel.

Now, we are ready to add the UNIQUE key.

Click on the Add key button and expand the data. Don’t forget to name your UNIQUE key constraint by filling in the Name field. To give it some context, use the Comment field. And, most importantly, add the column(s) that will implement this constraint.

That’s all! The UNIQUE key constraint is now ready.

SQL Code is Ready

You can also generate an SQL code like this:

And here it is:

In the next section, you’ll learn about the additional options available in various database engines.

Advanced Options in Various Database Engines

There are quite a few advanced constraint options that are database-specific. Let’s look at them one by one.

Available In	Option	Description
PostgreSQL	Deferrable	The available options are DEFERRABLE and NOT DEFERRABLE. DEFERRABLE means that verification of the constraint takes place after the transaction finishes.  NOT DEFERRABLE indicates that the constraint verification is performed immediately after each statement.
	Initially deferred	Here, we have either INITIALLY DEFERRED or INITIALLY IMMEDIATE. These options apply only if the constraint is DEFERRABLE. INITIALLY DEFERRED means that the constraint check is after every transaction. INITIALLY IMMEDIATE indicates that the constraint check is after each statement.
	Index tablespace	This option lets us define the tablespace where the unique index (associated with the UNIQUE key) resides.
	With	This clause is optional. It specifies storage parameters for a table or index.
MySQL	Using	This clause lets us define the type of the UNIQUE key index, e.g. BTREE or HASH.
	Key block size	This specifies the size of index key blocks in bytes. The database engine treats it as a hint.
Microsoft SQL Server	Is clustered	This indicates whether we deal with a clustered or non-clustered index.
	WITH index options	The WITH clause lets you specify index options, such as FILLFACTOR, PAD_INDEX, or ONLINE.
	ON clause	The ON clause lets you specify the partition scheme name, filegroup name, or default filegroup.

In Vertabelo, you can define your database when creating a new physical model. All the UNIQUE key constraint options are available in the right-side panel.

For PostgreSQL database engine:

For more info, see our article on database constraints in PostgreSQL.

For the MySQL database engine:

For further reading, we have an article about database constraints in MySQL.

For the Microsoft SQL Server database engine:

And here’s an article about Microsoft SQL Server database constraints.

Database constraints are a crucial part of any database design. Make sure to check out our database-specific articles to learn more about the different constraints available.

Learn More About UNIQUE and Other Database Constraints

The UNIQUE key constraint is a very straightforward concept. It simply prevents duplicate values in a column. Try out some examples on your own and you’ll see!

There are many more database constraints, such as primary and foreign keys, the CHECK constraint, the DEFAULT constraint, and the NOT NULL constraint. Continue to our article on Database Constraints: What They Are and How to Define Them in Vertabelo to get a glimpse of them all.

Good luck!