DEV Community

Cover image for Constraints in SQL
Damian Brdej
Damian Brdej

Posted on

Constraints in SQL

Constraints in SQL are used to specify rules for data. You can't insert a row if any of these rules are broken. It's a good way to prevent user from inserting wrong data.
Here's CREATE TABLE statement with most common constraints in it:

CREATE TABLE user(
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    status_id INT NOT NULL DEFAULT 1,
    age INT NOT NULL,
    CHECK (age >= 18)
    FOREIGN KEY (status_id) REFERENCES status(id)
)
Enter fullscreen mode Exit fullscreen mode

NOT NULL

With this constraint you can ensure that every row in this column has value, and cannot be NULL.

UNIQUE

Specify that every value in this column has different value.

PRIMARY KEY

Is a combination of NOT NULL and UNIQUE, often use to identify a specific row in table. There can be only one PRIMARY KEY in each table.

FOREIGN KEY

Prevents from destroying links between two tables when deleting or modifying data.

CHECK

Ensures that value of this field meets certain condition. In our example we check if the user has minimum 18 years old

DEFAULT

It's simply default value for field, if no value is specified.

Top comments (0)