Mastering SQL UNIQUE Constraints
for data integrity

No doubt, we have entered a digital age a couple of decades ago. But even since then, our relationship with data has changed drastically. Today, data has become a cornerstone of almost every aspect of modern life: from business to personal. Therefore, it is hard to overestimate the importance of its integrity, reliability, and uniqueness within a database.

In the article, we are focusing on the UNIQUE constraint, its syntax, basic and advanced use cases, as well as best practices and tools that work perfectly with the tasks at hand. Buckle up, as we are going to make this journey quick and informative.

Understanding SQL UNIQUE constraint

As the name suggests, the UNIQUE constraint in SQL allows you to make sure that all values in a column are distinct. Unlike PRIMARY KEY, which uniquely identifies each row and implicitly enforces NOT NULL, UNIQUE is not as restrictive. It allows NULL values and multiple UNIQUE constraints, while there can be only one PRIMARY KEY. The basic syntax can be as follows:

CREATE TABLE table_name (
  column1 datatype UNIQUE
 ,column2 datatype
 ,column3 datatype
 , ...
);

In the syntax above:

• column1 — the column to which the UNIQUE constraint applies.
• datatype — specifies the data type of the column.
• UNIQUE — the hero of the occasion, the constraint that indicates that the values in the column must be unique.

Try it yourself with dbForge Studio

Even though SQL Server Management Studio (SSMS) is the most popular and familiar tool that allows you to work with SQL Server databases, it is not the only one. Moreover, in the continuously evolving world of database development, administration, and management, new GUIs keep appearing like mushrooms after the rain. How do you choose the tool that is perfect for you in this variety?

Let us compare dbForge Studio for SQL Server with SSMS so that you can make an informed decision on which solution best aligns with your daily requirements:

Take advantage of dbForge Studio for SQL Server by downloading a free fully-functional 30-day trial version and installing it on your computer. With a huge pack of advanced features and intuitive GUI, this all-in-one MSSQL tool can maximize productivity and make SQL Server database development, administration, and management process efficient. The Studio can also be of use when it comes to today's topic, from generating test data to performing advanced percentage calculations.

For a more visual comparison of the two solutions, watch the SSMS vs dbForge Studio for SQL Server - Features Comparison video on the Devart YouTube channel.

Applying UNIQUE constraint in SQL Server

While you can still go about creating a table in an old-reliable way — using the CREATE TABLE SQL query — there are alternative approaches that you may find more convenient. For example, you can benefit from using such GUI tools as SSMS or dbForge Studio for SQL Server that can speed up almost any database experience, such as designing databases, writing SQL code, comparing databases, synchronizing schemas and data, generating meaningful test data and ready-made DDL and DML statements, and much more. In this article, we will be showcasing dbForge Studio for SQL Server to demonstrate the UNIQUE constraint usage in detail and provide you with tips on how to do it in the best way possible.

Creating a new table with the UNIQUE constraint

For starters, let us create a new table with a UNIQUE constraint:

1. In Object Explorer, right-click the database where you want to create the table and select New Table.

3. In the Table Designer that opens, specify the table name and type, schema, and description. Then, add columns to define the structure of your table and specify its name, datatype, whether it can contain NULL values or not.

You will see the detailed column properties in the corresponding window on the right, along with the CREATE TABLE script generated according to the settings below.

4. Go to the Constraints window, make a right click, and select New Unique Key.

5. Specify the desired column in the Constraint Columns box.

6. Click Apply Changes.

Just like that, we have created a table with the ID column as a primary key and the Email column with the UNIQUE constraint.

Adding the UNIQUE constraint to an existing table

Now, let us assume we created the same test table but forgot to add the UNIQUE constraint this time. What do we do in this case? We should certainly not start the table creation process all over again. We can easily edit the existing table to meet our requirements.

1. Expand the desired table in Object Explorer. Right-click the Keys folder and select New Unique Key.

2. Configure the unique key the same way you would do that for a new table.

3. Click Apply Changes.

Advanced usage of UNIQUE constraint

Having covered the basics, we are now free to try something more complex. We prepared two illustrative examples for you to try out. This section will describe how to apply the UNIQUE constraint to multiple columns and how to handle NULL values.

Using the UNIQUE constraint on multiple columns

You can apply the UNIQUE constraint on multiple columns, thus enforcing uniqueness across a number of values. The example below demonstrates the creation of an Employees table with the following columns: EmployeeID, FirstName, and LastName. To ensure that each combination of first and last names is unique, we apply the UNIQUE constraint to the corresponding columns:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY
 ,FirstName VARCHAR(50)
 ,LastName VARCHAR(50)
 ,UNIQUE (FirstName, LastName)
);

Handling NULL values with the UNIQUE constraint

Another tricky example would be dealing with the NULL values when they are faced with UNIQUE. By default, the UNIQUE constraint treats all NULL values as distinct. It means that a column with a UNIQUE constraint can have multiple NULL values because they are considered different from any other value, including another NULL.

In the following scenario, multiple employees can have NULL email addresses, but if an email is provided, it must be unique across all rows.

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY
 ,FirstName VARCHAR(50)
 ,LastName VARCHAR(50)
 ,Email VARCHAR(100) UNIQUE
);

However, if you want to allow only one NULL value in a column with a UNIQUE constraint, you would need to enforce this constraint using a combination of NULL checks and triggers, as the UNIQUE constraint itself does not provide this functionality out of the box.

Best practices for using UNIQUE constraints

In SQL, a WHERE clause filters a result set returned by DML statements such as SELECT, UPDATE, and DELETE. It works by setting conditions to specify which records to include in the results. The syntax of the clause can be as follows:

• Choose the right columns: Apply UNIQUE constraints to columns that should contain unique values across rows, such as usernames, email addresses, or identification numbers.
• Review indexing: UNIQUE constraints usually create indexes automatically to enforce uniqueness efficiently. However, review index usage and consider creating additional indexes for columns frequently used in queries.
• Avoid overloading: Do not overload a single column with multiple constraints. If a column already serves a primary purpose, make sure adding a UNIQUE constraint will not create unnecessary complexity.
• Monitor the performance: Regularly monitor database performance, especially after adding UNIQUE constraints, to ensure there are no significant performance impacts. Indexing and constraint validation can affect performance, particularly on large tables.
• Optimize queries: Write efficient SQL queries and optimize them using appropriate indexing, query tuning techniques, and database engine features to enhance performance.
• Test and validate: Thoroughly test your database design and constraints to identify any potential issues early. Validate data before insertion to prevent violating constraints.

Another important tip to consider is when to use the UNIQUE constraint and when PRIMARY KEY is a better option. The choice depends on whether you need to uniquely identify each row or enforce uniqueness in specific columns:

• UNIQUE — Use when you want to enforce the uniqueness of values in one or more columns but do not necessarily need to identify each row uniquely. For example, for email addresses or usernames.
• PRIMARY KEY — Use when you need to uniquely identify each row in a table. It implies uniqueness and not null constraints. Typically, you designate a single or combination of columns as the primary key.

Practical examples and exercises

By now, we have covered quite a bit of theoretical and practical information. It is time to walk through some hands-on examples of implementing UNIQUE constraints using dbForge Studio for SQL Server. We will use the Employees table we created earlier in this article and fill it with test data.

1. To fill the Employees table with data, click New SQL and execute the following script.

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
  VALUES (1, 'John', 'Doe', '[email protected]'),
  (2, 'Jane', 'Smith', '[email protected]'),
  (3, 'Bob', 'Jones', '[email protected]');

2. Now, let us test the UNIQUE constraint in action by trying to insert a duplicate value into the Email column:

-- This query will fail due to the UNIQUE constraint on the Email column
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
  VALUES (4, 'Alice', 'Johnson', '[email protected]');

As you can see, SQL Server does not allow us to have duplicate values in the Email column.

Data Generator for SQL Server

In the previous section of this article, we discussed how to insert data into a table with the UNIQUE constraint using the INSERT command. However, there is another way of filling tables with realistic data: Data Generator for SQL Server. This functionality is available with the following products:

1. As a part of dbForge Studio, which also offers a whole range of tools for database developers and administrators, including:
   • SQL Coding Assistance
   • Source Control
   • Index Manager
   • Unit Test
   • Table and Database Designer
   • Data and Schema Compare
2. As a standalone solution — dbForge Data Generator, which also can be easily integrated with SQL Server Management Studio.

Let us say we have created an empty BicycleStore database. The screenshot below graphically demonstrates the structure of the database, including tables, columns, connections, data types, foreign keys, etc.

Now, it is time to populate the database with test data:

1. In the Tools menu, click New Data Generation. The Data Generator wizard will open.

2. Specify the connection and select the BicycleStore database.

3. Click Next. The Options page will appear. Set the required options here.

4. Click Open. After processing, you will be presented with the data generation result.

You can specify the tables that you want to populate by selecting the check box that is located next to the table name. Further, you can define how you want the data to be generated: click the table name in the tree view and specify the details in the settings pane. All the changes are displayed in real time.

5. On the Data Generator toolbar, click .

6. The Data Population Wizard will open. On the Output page, you can select how to manage the data population script:

• Open the data population script in the internal editor.
• Save the script to a file.
• Execute the data population script against the database.

Select a required option and click Next.

7. On the Options page, configure the synchronization options. Click Next.

7. On the Additional Scripts page, type or select the script to be executed before and/or after the data population. Click Next.

8. The Summary page allows you to see the details of an error or warning. When you are setting up the tables and columns that you want to populate, dbForge Studio displays warning and error messages to inform you when there may be a problem with the data generation.

9. Click Generate to finish the process.

Further learning

The following guides can be helpful when working with SQL queries:

• SQL Server Tutorials
• dbForge Studio Documentation
• dbForge Studio Video Tutorials
• Devart Academy