Skip to main content

SQL constraints

Understanding SQL constraints is crucial as they specify the rules concerning the data types in a table. They help ensure the accuracy and reliability of the data contained within the table. In this tutorial, we are going to explore the various types of SQL constraints and how they are used in PostgreSQL.

What are SQL Constraints?

In simple terms, SQL constraints are rules that we can apply to the data in our SQL tables to ensure data integrity and to prevent the entry of incorrect or damaging data into our tables. These rules can be applied to a column or a whole table, and they are enforced whenever data is added, updated, or deleted.

Types of SQL Constraints

There are several types of SQL constraints. Here are the most common ones:

  1. NOT NULL Constraint: Ensures that a column cannot have a NULL value.
  2. UNIQUE Constraint: Ensures that all values in a column are distinct.
  3. PRIMARY KEY Constraint: Uniquely identifies each record in a database table. It must contain unique values and it cannot contain NULL values.
  4. FOREIGN KEY Constraint: Prevents actions that would destroy links between tables.
  5. CHECK Constraint: Ensures that all values in a column satisfy certain conditions.
  6. DEFAULT Constraint: Provides a default value for a column when none is specified.

Adding SQL Constraints

Now, let's understand how to add these constraints to our SQL tables in PostgreSQL.

NOT NULL Constraint​

In PostgreSQL, you can add a NOT NULL constraint to a column during the table creation. Here is the syntax:

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
);

UNIQUE Constraint​

The UNIQUE constraint can be added to a column during table creation or later by altering the table. Here is how to add a UNIQUE constraint during table creation:

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

PRIMARY KEY Constraint​

You can add the PRIMARY KEY constraint to a column during table creation. Here's the syntax:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
);

FOREIGN KEY Constraint​

You can add a FOREIGN KEY constraint to a column during the table creation. Here is the syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column1) REFERENCES other_table (column1)
);

CHECK Constraint​

The CHECK constraint can be added during table creation or later by altering the table. Here is how to add a CHECK constraint during table creation:

CREATE TABLE table_name (
    column1 datatype CHECK (condition),
    column2 datatype,
);

DEFAULT Constraint​

You can add a DEFAULT constraint to a column during table creation. Here is the syntax:

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    column2 datatype,
);

Conclusion

SQL constraints are a powerful tool for managing the integrity of the data in your PostgreSQL database. By using constraints, you can ensure that your data is accurate, consistent, and reliable. So, make sure to utilize them in your database design and management process.

Keep practicing and exploring different constraints, and soon you'll get a good grip on their usage. Happy Coding!