Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Constraints

Introduction
CHECK constraints with a simple condition
CHECK constraints with complex conditions
Adding CHECK constraints to existing tables
10. Adding CHECK constraints to existing tables
Summary

Instruction

Great! We already know a lot about creating CHECK constraints when defining tables, but what about adding them to existing tables? Let's say we have the following table:

CREATE TABLE video_game_ranking (
  game_id integer,
  user_rating decimal(4, 1) ,
  expert_rating decimal(4, 1),
  rank integer,
  award_count integer CHECK (award_count > 0)
);

We want to add the following the CHECK constraint: the rank column must be greater than 0:

ALTER TABLE video_game_ranking
ADD CHECK (rank > 0);

The code above resembles the one we wrote to add a UNIQUE constraint. You can also modify it and provide your own name for a constraint:

ALTER TABLE video_game_ranking
ADD CONSTRAINT rank_check CHECK (rank > 0);

Exercise

The following table was created by a train company:

CREATE TABLE train_connection (
  id integer PRIMARY KEY,
  city_from varchar(32),
  city_to varchar(32),
  distance_miles integer,
  basic_price decimal(5, 2) CHECK (basic_price > 0.0)
);

Your task is to add a constraint on the distance_miles column: it should always be greater than 0.