How to Use Unique Key Constraints in Database Tables

In this blog, I'll guide you through using the Unique Constraint when creating a database table. Rather than going into detail about what the Unique Constraint does, I'll demonstrate its usage with a simple, real-world example.

Scenario: Imagine you're tasked with creating a table to store vehicle details. We'll keep the table structure simple, including fields for ID, Name, Colour, NumberPlate, and CreatedDate. Let's dive in and create the table.

As shown in the image, we have a simple SQL script that creates the Vehicle table with all the required columns, and we've designated the Id column as the primary key.

Once deployed to production, it will function correctly, storing records in the table without issues, provided the values are accurate.

However in our "real world scenario" we know that the number plate is unique to each vehicle, so what happens in our table when we get two different vehicle records with the same number plate?

Let's test this scenario by inserting some records into the Vehicles table, as shown below:

As shown above, we've created three new records in the Vehicles table. Records one and three share the same number plate, which isn't ideal in a real-world scenario, as the number plate must be unique to each vehicle.

So, how do we ensure that the NumberPlate column contains no duplicate data? This is where Unique constraints play a crucial role. Let's delete and recreate the table, this time adding a constraint on the NumberPlate column.

As shown above, we've recreated the table and added a Unique Constraint to the NumberPlate column. This means that every vehicle's number plate must be unique, or the record will be rejected.

Now that we've successfully recreated the table, re-run the previous insert statements and observe the results.

You'll notice two things: first, a 'Violation of UNIQUE KEY constraint' error will occur, and second, only two out of the three records will be inserted. The record that violated the UNIQUE KEY constraint will be rejected, as shown below.

In a nutshell, a Unique Key constraint ensures that no two rows can have the same combination of values in the specified columns. This prevents duplicates and helps maintain data integrity. Thank you for taking the time to read my blog—I hope you found it helpful. Happy coding!