PostgreSQL Constraints: Types with Example

What is Constraint?

Data types are used to limit the kind of information that can be stored in a table. But there are 2 issues with this approach.

• Issue 1: Restraints enforced by data types are not adequate for certain applications. For example, a column containing an Item price should only accept positive values. However, there is no specific data type which only accepts the positive number.
• Issue 2: You may want to limit the information in a row/column data in relation to other columns or rows. For example, a table containing Item information should have only one row for each unique column constraint.

To overcome such issues and define these rules, you can use constraints in PostgreSQL. In simple terms, constraints are rules that your data has to follow. Adding table constraints enables the database system to enforce data integrity.

Therefore, if a user is trying to store data in a column that violates a defined constraint, an error message should be displayed.

Type of PostgreSQL Constraints

Let’s take a look at various types of PostgreSQL constraint you can create to ensure data correctness:

• Unique
• Not Null
• Check Constraint
• Primary Key
• Foreign Key
• Execution Constraint

1) PostgreSQL Unique Constraint

Unique constraints help you ensure that the data contained in a column, or a group of column constraints is unique.

Example

CREATE TABLE Item(
Item_no integer UNIQUE,
Name text,
);

2) NOT Null Constraints

A not-null constraint defines that a column should never be a null value. This type of constraint is very much similar to creating a check constraint. However, PostgreSQL creates a not-null constraint, which is more efficient. The problem with this method is that you can’t give explicit names to not-null constraints.

The not null constraint is not a defaulted PostgreSQL standard and should not be used in portable applications. It was later added in PostgreSQL to make it compatible with some other DBMS systems. Many use it because it makes it easy to toggle the constraint in a script file.

Syntax:

CREATE TABLE Item (
product no integer NULL, Item_name text NULL, Itm_price numeric NULL
);

3) PostgreSQL Check Constraints

A check constraint helps you to specify that the value in some column must be a Boolean expression.

The PostgreSQL check constraint consists of the CHECK keyword, which is followed by an expression in parentheses. The check constraint Postgres should involve the column that should be constrained else it doesn’t make any sense.

Syntax:

CREATE TABLE Item(
Item_id INTEGER PRIMARY KEY,
name VARCHAR(20),
Item_price NUMERIC CHECK(price>0)
);

Example:

CREATE TABLE Emp(
ID	INTEGER primary key,
First_name VARCHAR(20),
Last_name VARCHAR(20),
Gender CHAR(l) check(gender gender='M'),
Salary INTEGER NOT NULL,
Dept_Num INTEGER
);

CREATE TABLE ITEM(
Id INTEGER PRIMARY KEY,
name VARCHAR(15),
price numeric CHECK (price > 0)
);

4) Primary Key Constraints

A primary key constraint allows you to use a column or group of columns as a unique identifier for rows in the table. To define primary key constraints, your declared values should be both unique and not null. This allows two table definitions to accept the same data.

Syntax:

Create Table Item(
Item_no, integer PRIMARY KEY,
Item_name text,
Item_Price numeric
);

Example 1:

CREATE TABLE Employee(
ID INTEGER PRIMARY KEY
Fname VARCHAR(20),
Lname VARCHAR(20),
Gender CHAR(l),
Salary INTEGER NOT NULL, Dept INTEGER, UNIQUE(FNAME, LNAME)
);

Example 2:

CREATE TABLE first(
A1 INTEGER. A2 INTEGER. PRIMARY KEY (tl)
);
Second way CREATE TABLE test( tl INTEGER PRIMARY KEY, t2 INTEGER.
);

Example 3:

CREATE TABLE Items (
Item_no integer UNIQUE NOT NULL, name text, price numeric
CREATE TABLE Items (
Items_no no integer PRIMARY KEY, name text, price numeric

5) Foreign Key Constraints

A foreign key constraint specifies that the values in a column or a group of columns have to match the values appearing in some row of another table. This allows you to establish the referential integrity between two associated tables.

Let’s assume you have a table that stores orders for different products. You want to ensure that the table contains orders of products that exist. So, here, you need to define a foreign key constraint in the orders table that references the products table.

Syntax:

CREATE TABLE	(
id INTEGER PRIMARY KEY. name VARCHAR(IO)
>;
CREATE TABLE testable
Id INTEGER PRIMARY KEY
read INTEGER REFERENCES	(id)
);

Example 2:

CREATE TABLE Student (
Std_ID INTEGER primary key,
First_name VARCHAR(20),
Last_name VARCHAR(20),
Gender CHAR(l),
Steam, VARCHAR(20),
Dept_NUM INTEGER REFERENCES Department
);

6) Exclusion Constraints

Exclusion constraints help you to make sure that if any two rows are compared with each other on the specified columns or expressions with specified operators, then at least one of these operator comparisons will return a null or false value. Adding this PostgreSQL constraint will automatically create an index of the type specified in constraint declaration.

Example:

For example, the following PostgreSQL statement creates a new table called School and adds five columns.

CREATE TABLE SCHOOL6(
STDID INT PRIMARY KEY NOT NULL,
STDNAME TEXT NOT NULL,
STDAGE INT NOT NULL,
FEEDBACK CHAR(50),
STANDARD INT NOT NULL,

For example, the following PostgreSQL statement creates a new table called Branch, which adds three columns. The column STD_ID is the foreign key, and it references the ID field of the table SCHOOL6.

CREATE TABLE BRANCH (
BRANCHCODE INT PRIMARY KEY NOT NULL,
BRAMCHNAME CHAR(50) NOT NULL,
STD_ID INT references SCHOOL6(ID)
);