DEV Community

Cover image for How to Use Unique Key Constraints in Database Tables
Abraham Lukwesa
Abraham Lukwesa

Posted on

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.

Image description

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:

Image description

Image description

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.

Image description

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.

Image description

Image description

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!

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 👀

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay