Essential SQL Constraints to Keep Your Data Clean and Reliable

Essential SQL Constraints to Keep Your Data Clean and Reliable

Akshobya KL's photo
Akshobya KL
·

3 min read

Now that we have set up our tables and picked the right data types, it's time to make sure the data entering those tables is valid. That’s where SQL constraints come in. Constraints let us define rules that our data must follow, to keep our database tidy and reliable.

Why Do Constraints Matter?

SQL constraints act like a safety net for our data. Without them, we could end up with duplicate entries, missing important information or invalid relationships between tables. Over time, this can lead to inconsistent data, which is a nightmare to clean up.

Let's go through some of the most common SQL constraints, how we can use them to keep our data in check.

Constraints in sql

1. PRIMARY KEY

A PRIMARY KEY is like an ID badge for each record in our table it makes sure every row is unique and easily identifiable.

  • What it does: Guarantees that no two records in a table have the same identifier.

  • Real-world use case: In a users table, we might use id as the primary key.

id INT PRIMARY KEY,
    userCREATE TABLE users (
    name VARCHAR(50) NOT NULL
);

Here, id uniquely identifies each user, and no two users can have the same id. Plus, the primary key ensures id can’t be left blank.

2. FOREIGN KEY

A FOREIGN KEY is like a link between two tables. It ensures that the value in one table exists in another, establishing relationships between data.

  • What it does: Links columns between tables, ensuring valid relationships.

  • Real-world use case: A posts table where each post is tied to a valid user_id.

CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    user_id INT,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

With this constraint, every user_id in the posts table has to correspond to an actual id in the users table.

3. NOT NULL

The NOT NULL constraint is simple but powerful. It makes sure that certain columns can't be left empty. This constraint guarantees they’re always filled out.

  • What it does: Ensures that a column always has a value.

  • Real-world use case: Requiring every user to have a username.

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

4. UNIQUE

The UNIQUE constraint keeps our data unique. It’s perfect for when we need to ensure that no two entries have the same value. For instance, we don’t want two users with the same email address.

  • What it does: Ensures that all values in a column are distinct.

  • Real-world use case: Preventing duplicate email addresses.

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

using UNIQUE, we prevent duplicates and guarantee that every email is one of a kind.

5. CHECK

The CHECK constraint lets us set custom rules that our data must follow. This is super useful when we want to enforce certain boundaries like making sure age is realistic.

  • What it does: Validates data based on a condition we define.

  • Real-world use case: Ensuring age values are within a reasonable range.

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age > 0 AND age < 120)
);

This ensures that we don't accidentally enter an age of 200 or a negative value keeping our data accurate.

6. The DEFAULT constraint in SQL is used to automatically assign a default value to a column if no value is specified when a record is inserted. It’s a simple but effective way to ensure that columns always have some data, even when we don’t explicitly provide it.

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
);
SQL