Alter Table Add Constraint - How to Use Constraints SQL

Introduction

When working with databases you’ll need to manage the data in the database by defining the state of the specific data in a given column. There are several constraint put in data when using PostgreSQL. They include the PRIMARY KEY constraints, FOREIGN KEY constraint, CHECK constraint, UNIQUE constraint and the NOT NULL constraint. Some constraints such as the PRIMARY KEY and the FOREIGN KEY are useful when defining and managing the relationship between data in various tables in a database. In this tutorial we shall focus on the commands used to add constraints to table columns.

Basically, we use the ALTER TABLE ADD CONSTRAINT command to put specific constraint on a given table column.

The syntax to add constraints to a table column is as follows:

ALTER TABLE TABLE_NAME
ADD constaint_type (column_name);

Let’s now look at each constraint mentioned above while giving illustrations of how you can add them.

Adding primary key constraints

To add a primary key constraint, we use the following syntax:

ALTER TABLE tablename
ADD PRIMARY KEY (column_name);

Let’s start by creating a books table with book_id and the book_name columns using the following statement:

CREATE TABLE books (
   book_id SERIAL,
   book_name VARCHAR
);

Let’s display the table structure and view it before we alter any state:

articles=# \d books;
                                     Table "public.books"
  Column   |       Type        | Collation | Nullable |                Default
-----------+-------------------+-----------+----------+----------------------------------------
 book_id   | integer           |           | not null | nextval('books_book_id_seq'::regclass)
 book_name | character varying |           |          |
Then, we will add the primary key constraint to the book_id column as shown below:
ALTER TABLE books
ADD PRIMARY KEY (book_id);
The following changes appear after executing the above command:
articles=# \d books;
                                     Table "public.books"
  Column   |       Type        | Collation | Nullable |                Default
-----------+-------------------+-----------+----------+----------------------------------------
 book_id   | integer           |           | not null | nextval('books_book_id_seq'::regclass)
 book_name | character varying |           |          |
Indexes:
    "books_pkey" PRIMARY KEY, btree (book_id)

Adding FOREIGN KEY constraint

Foreign key refers to a field or a set of fields in a table that uniquely identifies another row in another table. Normally, a foreign key in one table points to a primary key on the other table.

Foreign keys are added into an existing table using the ALTER TABLE statement. The following syntax is used:

ALTER TABLE child_table
ADD CONSTRAINT constraint_name FOREIGN KEY (c1) REFERENCES parent_table (p1);

In the above syntax, the child_table is the table that will contain the foreign key while the parent table shall have the primary keys. C1 and p1 are the columns from the child_table and the parent_table columns respectively. Let us now add foreign key to an existing table in an example:

ALTER TABLE books
ADD FOREIGN KEY (book_id) REFERENCES library (book_id);
The result is as shown below:
articles=# \d books;
                                     Table "public.books"
  Column   |       Type        | Collation | Nullable |                Default
-----------+-------------------+-----------+----------+----------------------------------------
 book_id   | integer           |           | not null | nextval('books_book_id_seq'::regclass)
 book_name | character varying |           |          |
Indexes:
    "books_pkey" PRIMARY KEY, btree (book_id)
Foreign-key constraints:
    "books_book_id_fkey" FOREIGN KEY (book_id) REFERENCES library

ADD CHECK CONSTRAINT

CHECK constraint check whether the data in the column has met a given specification. The following syntax is used to add check constraint:

ALTER TABLE TABLE _name
ADD CONSTRAINT constaint_name  CHECK (CONSTRAINT);

Example

ALTER TABLE student
ADD CONSTRAINT age_constraint CHECK (age >= 10);

The result of executing the above command is as show below:

articles=# \d student;
                                   Table "public.student"
 Column |         Type          | Collation | Nullable |               Default
--------+-----------------------+-----------+----------+-------------------------------------
 id     | integer               |           | not null | nextval('student_id_seq'::regclass)
 name   | character varying(50) |           | not null |
 age    | integer               |           |          |
 gender | character varying(50) |           | not null |
 marks  | double precision      |           |          |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "age_constraint" CHECK (age >= 10)

ADD UNIQUE constraints

UNIQUE constraint is used when we want to ensure that the data such as email or username is unique across all the table rows. We use the syntax below to add a UNIQUE constraint to a given column:

ALTER TABLE TABLE_NAME
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

Example

ALTER TABLE teacher_details
ADD CONSTRAINT email_unique UNIQUE (email);

Below are the results of adding unique constraint in our teacher_details table:

articles=# \d teacher_details;
                                            Table "public.teacher_details"
       Column        |       Type        | Collation | Nullable |                       Default
---------------------+-------------------+-----------+----------+-----------------------------------------------------
 teacher_id          | integer           |           | not null | nextval('teacher_details_teacher_id_seq'::regclass)
 teacher_name        | character varying |           | not null |
 registration_number | character varying |           |          |
 email               | character varying |           |          |
 class_id            | character varying |           |          |
 phone_number        | character varying |           |          |
Indexes:
    "teacher_details_pkey" PRIMARY KEY, btree (teacher_id)
    "email_unique" UNIQUE CONSTRAINT, btree (email)

NOT NULL Constraint

NOT NULL constaint defines a column that should not accept null. Null means that the field has no value in it. It is however not equal to empty string or even zero. We may have encountered this type of constraint in our previous commands. The syntax to set NOT NULL constraint on an existing table column is as given below:

ALTER TABLE TABLE_NAME
ALTER COLUMN column_name SET NOT NULL;

Example

ALTER  TABLE teacher_details
ALTER COLUMN email SET NOT NULL;

The result will be as shown in the image below:

Image from Gyazo

Conclusion

In this tutorial, we have covered a set of database constraints and their usage.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.