DEV Community

Cover image for Constraints in SQL
Damian Brdej
Damian Brdej

Posted on

2 1

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.

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs