---
description: 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.
title: PostgreSQL Constraints: Types with Example
image: https://www.guru99.com/images/postgresql-constraints.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

PostgreSQL Constraints enforce six built-in integrity rules at the database level: UNIQUE, NOT NULL, CHECK, PRIMARY KEY, FOREIGN KEY, and EXCLUSION. Lessons cover syntax, examples, and when to apply each rule.

* 🛡️ **Integrity Rules:** Blocks invalid data at DB level.
* 🔑 **Primary Key:** UNIQUE plus NOT NULL; one per table.
* 🔗 **Foreign Key:** Points to UNIQUE or PRIMARY KEY elsewhere.
* ✅ **CHECK:** Boolean per-row check.
* 🚫 **NOT NULL:** Rejects NULL values.
* 🎯 **EXCLUSION:** UNIQUE with custom operators.

[ Read More ](javascript:void%280%29;) 

![PostgreSQL Constraints](https://www.guru99.com/images/postgresql-constraints.png)

## 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 _constraint_s 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

## 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,
);

## PostgreSQL NOT NULL Constraint

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](https://www.guru99.com/what-is-dbms.html). 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
);

### RELATED ARTICLES

* [PostgreSQL vs MySQL – Difference Between Them ](https://www.guru99.com/postgresql-vs-mysql-difference.html "PostgreSQL vs MySQL – Difference Between Them")
* [PostgreSQL LIKE, Not Like, Wildcards (%, \_ ) Examples ](https://www.guru99.com/postgresql-like-query.html "PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples")
* [PostgreSQL Triggers: Create, List & Drop with Example ](https://www.guru99.com/postgresql-trigger-create-drop.html "PostgreSQL Triggers: Create, List & Drop with Example")
* [PostgreSQL ALTER TABLE: Add & Rename Column ](https://www.guru99.com/postgresql-alter-add-rename-column-table.html "PostgreSQL ALTER TABLE: Add & Rename Column")

## PostgreSQL CHECK Constraint

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)
);

## PostgreSQL PRIMARY KEY Constraint

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

## PostgreSQL FOREIGN KEY Constraint

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
);

## PostgreSQL EXCLUSION Constraint

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)
);

## FAQs

⚡ What are the six PostgreSQL constraint types?

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, EXCLUDE.

🤖 How does AI help write PostgreSQL constraints?

AI tools like Copilot generate CREATE TABLE statements with constraints and CHECK conditions.

💡 Can AI detect missing constraints in existing schemas?

Yes. AI tools scan schemas for orphaned foreign keys, missing NOT NULL, and tables without primary keys.

🔑 PRIMARY KEY vs UNIQUE?

PRIMARY KEY combines UNIQUE and NOT NULL; one per table. UNIQUE allows multiple and accepts NULL.

🔗 What if a foreign key reference is deleted?

Use ON DELETE CASCADE, SET NULL, or RESTRICT.

✅ Can CHECK reference other tables?

No. CHECK references same-row columns only. Use foreign keys or triggers.

🎯 When should I use EXCLUDE instead of UNIQUE?

Use EXCLUDE when uniqueness needs non-equality operators (e.g. && for overlapping ranges).

📚 Can I add a constraint to an existing table?

Yes. ALTER TABLE ADD CONSTRAINT works after creation. Use NOT VALID to skip checking existing rows.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/postgresql-constraints.png","url":"https://www.guru99.com/images/postgresql-constraints.png","width":"700","height":"250","caption":"PostgreSQL Constraints","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/postgresql-constraints.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/postgresql","name":"PostgreSQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/postgresql-constraints.html","name":"PostgreSQL Constraints: Types with Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/postgresql-constraints.html#webpage","url":"https://www.guru99.com/postgresql-constraints.html","name":"PostgreSQL Constraints: Types with Example","dateModified":"2026-06-23T17:16:30+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/postgresql-constraints.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/postgresql-constraints.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow","description":"I am Juniper Willow, a PostgreSQL Developer, offering expert guidance to help you optimize and master PostgreSQL database development.","url":"https://www.guru99.com/author/juniper","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/juniper-willow-author.png","url":"https://www.guru99.com/images/juniper-willow-author.png","caption":"Juniper Willow","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"PostgreSQL","headline":"PostgreSQL Constraints: Types with Example","description":"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.","keywords":"postgresql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/juniper","name":"Juniper Willow"},"dateModified":"2026-06-23T17:16:30+05:30","image":{"@id":"https://www.guru99.com/images/postgresql-constraints.png"},"copyrightYear":"2026","name":"PostgreSQL Constraints: Types with Example","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Six PostgreSQL constraint types?","acceptedAnswer":{"@type":"Answer","text":"PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, EXCLUDE."}},{"@type":"Question","name":"How does AI help with constraints?","acceptedAnswer":{"@type":"Answer","text":"AI generates CREATE TABLE with constraints and suggests CHECK conditions from sample data."}},{"@type":"Question","name":"Can AI detect missing constraints?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI tools flag orphaned keys, missing NOT NULL, and tables without primary keys."}},{"@type":"Question","name":"PRIMARY KEY vs UNIQUE?","acceptedAnswer":{"@type":"Answer","text":"PRIMARY KEY is UNIQUE plus NOT NULL. UNIQUE allows multiple per table and accepts NULL."}},{"@type":"Question","name":"What if foreign key reference is deleted?","acceptedAnswer":{"@type":"Answer","text":"Default raises error. Use ON DELETE CASCADE, SET NULL, or RESTRICT."}},{"@type":"Question","name":"Can CHECK reference other tables?","acceptedAnswer":{"@type":"Answer","text":"No. Use foreign keys or triggers for cross-table validation."}},{"@type":"Question","name":"When to use EXCLUDE?","acceptedAnswer":{"@type":"Answer","text":"When uniqueness needs a non-equality operator, like && for range overlap."}},{"@type":"Question","name":"Add constraint to existing table?","acceptedAnswer":{"@type":"Answer","text":"ALTER TABLE ... ADD CONSTRAINT. Use NOT VALID to skip existing-row validation."}}]}],"@id":"https://www.guru99.com/postgresql-constraints.html#schema-1118972","isPartOf":{"@id":"https://www.guru99.com/postgresql-constraints.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/postgresql-constraints.html#webpage"}}]}
```