Working with Constraints in MySQL

Intro

Constraints are rules you can place on tables to prevent invalid data from being inserted. By using constraints we can prevent columns from being empty, we can ensure values or unique, and much more. In this article, we will learn how to use Constraints in Postgresql.

Types of Constraints

A list of the most common constraints are as follows:

• Not null
• Unique
• Primary key: adds a unique id to a row
• Foreign Key: ensures a row exists in another table
• Check: a dynamic check for inserting rows
• Exclusion

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: mysql:latest
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: root_pass
      MYSQL_DATABASE: app_db
      MYSQL_USER: db_user
      MYSQL_PASSWORD: db_user_pass
    ports:
      - "6033:3306"
    volumes:
      - dbdata:/var/lib/mysql
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: pma
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  dbdata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

Not Null Constraint

If we want to prevent columns from being empty when inserting rows into the database we can use the NOT NULL constraint. Here is an example of creating a table where we don't allow the last name to be null.

CREATE TABLE employees (
    last_name   VARCHAR(16)     NOT NULL,
);

Unique Constraint

The unique constraint allows us to prevent duplicates for a particular columns. For example, let's update our exmployee table to have unique email addresses.

CREATE TABLE employees (
    last_name   VARCHAR(16)     NOT NULL,
    email       VARCHAR(16)     NOT NULL UNIQUE,
);

Primary Key Constraint

The Primary Key constraint allows us to create a specific column to identify a row. It is common practice to use a column name id but that is not always the case. There can only be one primary key, unlike unique which we can have many unique columns.

CREATE TABLE employees (
    id          INT             PRIMARY KEY,
    last_name   VARCHAR(16)     NOT NULL,
    email       VARCHAR(16)     NOT NULL UNIQUE,
);

Foreign Key Constraint

A Frorein Key constraint is placed on a column or multiple columns that ensures they exist in another table. This allows us to keep relationships among tables. For example, we can create a salary table and add a foreign key to ensure an employee exists before we insert a salary.

CREATE TABLE salary (
    id          INT             PRIMARY KEY,
    amount      DECIMAL         NOT NULL,
    emp_id      INT             REFERENCES employees(id)
);

Check Constraint

The check constraint allows us to add custom conditions to our columns. For example, we can ensure our salaries don't go below a specific number.

CREATE TABLE salary (
    id          INT             PRIMARY KEY,
    amount      DECIMAL         CHECK(SALARY > 10000),
    emp_id      INT             REFERENCES employees(id)
);

Exclusion constraints

Exclusion constraints are a bit of a special case. They allow you to enforce comparisions and ensure that a comparision will return false or null. We also need the btree_gist example.

In this example, we prevent a salary from being inserted with the same amount and emp_id.

CREATE EXTENSION btree_gist;
CREATE TABLE salary(
  id          INT             PRIMARY KEY,
  amount      DECIMAL         CHECK(SALARY > 10000),
  emp_id      INT             REFERENCES employees(id)
  EXCLUDE USING gist (amount WITH=, emp_id WITH=)
);