Constraints in Postgresql

PostgreSQL offers support for constraints and has coverage of multiple-level constraints. Constraints are used to enforce rules on data insertion in tables. Only data that complies with the constraint rules is allowed to be added to the table.

The constraints present in PostgreSQL are:

• Unique constraints
• Not-null constraints
• Exclusion constrains
• Primary key constraints
• Foreign key constraints
• Check constraints

We will explain all of these constraints one by one with supportive examples. Let’s start with the unique constraints.

Unique constraints

A unique constraint is a constraint that at the time of an insertion operation makes sure that data present in a column (or a group of columns) is unique with regard to all rows already present in the table. Let’s create a few tables using unique constraints in the following manner:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER UNIQUE,
  tool_name TEXT,
  tool_class NUMERIC
  );

Alternatively, the same constraint can be declared at the end of all columns. For instance, this can look like the following:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER,
  tool_name TEXT,
  tool_class NUMERIC,
  UNIQUE (tool_id)
);

When defining the unique constraints for a group of columns, all columns must be listed separately using commas. Consider the following example:

warehouse_db=# CREATE TABLE cards
  (
  card_id INTEGER,
  owner_number INTEGER,
  owner_name TEXT,
  UNIQUE (card_id, owner_number)
  );

The preceding query will create the cards table with a unique constraint implemented on the card_id and owner_number columns. Note that the unique constraint is not applicable on null values. This means that in the cards table, two records can have the same record if they have card_id and owner_number as null.

not-null constraint in Postgresql

A not-null constraint makes sure that a column must have some values and a value is not left as null. Drop the previously created tools table and create the tools table again using this constraint using the following example:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER NOT NULL,
  tool_name TEXT,
  tool_class NUMERIC
  );

The preceding query will create a table with a not-null constraint on the tool_id column. We can apply the not-null constraint on as many columns as we can. Consider the following example:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER NOT NULL,
  tool_name TEXT NOT NULL,
  tool_class NUMERIC
  );

The preceding query will create the tools table with not-null constraints on tool_id and tool_name.

Exclusion Constraints in Postgresql

An exclusion constraint is used when comparing two rows on nominative columns or expressions using the nominative operators. The result of the comparison will be false or null.

Consider the following example in which the conflicting tuple is given the AND operation together:

warehouse_db=# CREATE TABLE movies
  (
  Title TEXT,
  Copies INTEGER
  );

Using the ALTER TABLE command,we get the following:

warehouse_db=# ALTER TABLE movies
  ADD EXCLUDE (title WITH=, copies WITH=);

We will create an exclusion constraint above the ALTER TABLE command. The conditions for a conflicting tuple are AND together. Now, in order for two records to conflict, we’ll use the following:

record1.title = record2.title AND record1.copies = record2.copies.

Next article, we'll consider primary and secondary key constraints.