How to use PostgreSQL Constraints

Introduction on How to use PostgreSQL Constraints

This tutorial will explain how to use the different PostgreSQL constraints in a table. SQL constraints are helpful in setting stricter limits on data types. On common example of this is the NOT NULL constraint that is used to prevent the insertion of a row or record into the constrained column without a value first being specified.

Prerequisites for using PostgreSQL

  • PostgreSQL must be properly installed and configured. Execute the service postgresql status command to confirm the PostgreSQL status is active. Press the CTRL + C keys to exit.

  • The interactive PSQL command-line for PostgreSQL must be properly installed and working. Executing the psql -V command, to confirm the installation is active, should produce the following results:

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Accessing the PostgreSQL using the ‘psql’ command-line interface

Execute the following command, via the psql command-line interface, to access the PostgreSQL database on the localhost server:

sudo su - postgres

Entering the password at the prompt and hitting the Enter key should grant access to the Postgres database.

Enter the following command to access the database:

psql some_username -h 127.0.0.1 -d some_database

The system will again prompt for the user’s password; enter the password and again hit the Enter key.

The Different Types of Constraints in PostgreSQL Table

  • NOT NULL: A column can hold NULL values by default. However, using the constraint NOT NULL will prevent a column from having a NULL value when using PostgreSQL constraints.
  • PRIMARY KEY: This specifies the uniqueness on each record in the table.
  • FOREIGN KEY: This identifies any table values that match to another column of another table.
  • UNIQUE: Will prevent different records from having identical values.
  • CHECK: Evaluates a condition where the value of “true” or “false” is being checked.

The Different Types of Constraints in PostgreSQL Table

About the ‘NOT NULL’ Constraint

The NOT NULL constraint requires a column to have a non-null value in the table.

The following code uses the not null value in a PostgreSQL table:

CREATE TABLE department(
id INT NOT NULL,
name TEXT,
address TEXT
);

NOTE: The NOT NULL constraint should be used after the data type to declare the constraint.

About the ‘PRIMARY KEY’ Constraint

The PRIMARY KEY serves as a unique identifier, just like record ID number, in a PostgreSQL table’s column. Here each key can only occur once in a particular table. Here is an example:

CREATE TABLE company(
id INT NOT NULL PRIMARY KEY,
name TEXT,
address VARCHAR(50)
);

NOTE: The PRIMARY KEY constraint, even though it is not explicitly stated, will still inherit the NOT NULL and UNIQUE constraint properties.

About the ‘FOREIGN KEY’ Constraint

A Foreign Key constraint defines the referencing table. A PostgreSQL table can have multiple foreign keys, depending on the table relationship.

In the following example, the “company” table provides the records to another table called “department:”

CREATE TABLE company(
id INT NOT NULL PRIMARY KEY,
name TEXT,
address VARCHAR(50)
);

Notice the department table contains another ID. This is when the foreign key constraint is presented, as shown here:

CREATE TABLE department(
id INT NOT NULL PRIMARY KEY,
dept_name TEXT
emp_id INT REFERENCES company(id)
);

NOTE: The word REFERENCES is used to relate data from one table to another.

About the ‘UNIQUE’ Constraint

Unique refers to the originality, or “uniqueness,” of the values of records in a PostgreSQL table. For example:

CREATE TABLE employee(
emp_id INT NOT NULL PRIMARY KEY,
name TEXT,
email VARCHAR(50) NOT NULL UNIQUE,
address VARCHAR(50),
salary REAL
);

Notice the email column cannot have duplicate emails since it has the UNIQUE constraint applied to it.

About the ‘CHECK’ Constraint

The value in the CHECK constraint uses the Boolean expression to indicate the value in the column must meet the specified requirements. Following is an example of the CHECK constraint:

CREATE TABLE employee(
emp_id INT NOT NULL PRIMARY KEY,
name TEXT,
email VARCHAR(50) NOT NULL UNIQUE,
address VARCHAR(50),
salary REAL CHECK(salary > 50000)
);

If data were now inserted into the table it would return an error. The following example shows data for the table employee:

INSERT INTO employee(emp_id, name, email, address, salary)
VALUES(1, 'Jerald', 'jerald@gmail.com', '123 higher st', 45234);

The result will return an error resemble the following:

ERROR: NEW ROW FOR relation "employee" violates CHECK CONSTRAINT "employee_salary_check"
DETAIL: Failing ROW contains (1, Jerald, jerald@gmail.com, 123 higher st, 45234).

Conclusion on How to use the PostgreSQL Constraints Table

This tutorial explained how to use the various PostgreSQL constraints in a table. The article explained how to access the PostgreSQL using the ‘psql’ command-line interface and explained the various types of PostgreSQL table constraints. These included the ‘NOT NULL’ constraint, the ‘PRIMARY KEY’ constraint, the ‘FOREIGN KEY’ constraint, the ‘UNIQUE’ constraint the ‘CHECK’ constraint. Remember that the PRIMARY KEY serves as an ID for a record in a PostgreSQL table’s column and, when used, each key can only occur once in a particular table.

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.