Constraints in PostgreSQL and How to Model Them in Vertabelo

Database constraints allow you to give a certain shape to your data, tables, and columns. Are you planning to use PostgreSQL as your database? Check out what PostgreSQL constraints can do!

In this article, we will go over PostgreSQL’s database constraints and see how to model them in Vertabelo. For each of the constraints, I’ll provide:

• A definition of the constraint.
• A usage example.
• Instructions on how to model the constraint in Vertabelo.

We’ll also cover the indexes used in PostgreSQL and how to set them up in Vertabelo.

Let’s get started.

Starting a PostgreSQL Database Model in Vertabelo

Vertabelo lets you choose a specific database when creating the database model. Let’s briefly go through the steps for the PostgreSQL database.

1. Choose Create new document from the toolbar.
2. Click on Create next to the Physical database model.
3. Provide the model name, choose the database engine PostgreSQL 9.x, and click the Start Modeling

Check out our article on How to Create Physical Diagrams in Vertabelo to find out more about the creation of physical diagrams.

Now we are ready to go!

PRIMARY KEY Constraint in PostgreSQL

The PRIMARY KEY constraint in PostgreSQL ensures the unique identification of every row in a particular table. It’s well known and has become practically mandatory. Each table can have only one primary key, which can consist of one column or a combination of columns.

Let’s look at the Book table below.

ISBN	Author	Title
0-8244-8247-6	Tim Brown	Diaries
0-4506-2393-9	Laura Anderson	How to program
0-6076-7451-2	Taylor White	Cooking recipes

Every book has its own ISBN number that uniquely identifies it. Hence, when creating the Book table, we can use the ISBN column as its primary key. Here, no other column is necessary in order to uniquely identify each row.

One could also identify each row of the Book table using a composite PRIMARY KEY. Instead of the ISBN column, the combination of Author and Title columns could identify each row uniquely as well. Of course, we’re assuming that each author only writes one book with a given title.

How to Model the PRIMARY KEY Constraint in Vertabelo

It is easy to create the PRIMARY KEY constraint in Vertabelo. Just check the PK box next to the column definition, as shown below.

Once you select the PK checkbox in the Columns section, the column is added in the Primary key section.

You could tick the boxes for several columns in one table to create a multicolumn PRIMARY KEY constraint.

The PRIMARY KEY constraint in PostgreSQL does not have additional database-specific features, as its usage is very basic (but crucial for database table creation).

Vertabelo can now generate SQL codes that include the definition of the PRIMARY KEY constraint as follows:

• For a single-column primary key:
• For a multicolumn primary key:

The PRIMARY KEY constraint is generated inside the CREATE TABLE statement.

You can also add a custom name for the PRIMARY KEY constraint in the Primary key section, as shown below.

FOREIGN KEY Constraint in PostgreSQL

The FOREIGN KEY constraint in PostgreSQL allows the creation of references between tables. References are created between one or more columns of the primary table and one or more columns of the foreign table.

The FOREIGN KEY of one table references the PRIMARY KEY of another table, i.e. the foreign key column of one table stores the values (or a subset of the values) of the primary key column of another table. This creates links between the particular rows of these tables. Using a FOREIGN KEY constraint allows the separation of data belonging to one object (e.g. an employee) into different tables that are connected using the FOREIGN KEY constraint.

For example, an employee has a name and contact information that could be stored in an EmployeeDetails table. But this employee also has position and salary information that we would like to store in the EmployeeSalary table. In such a case, you can relate the information stored in the EmployeeSalary table to employee records in the EmployeeDetails table using the FOREIGN KEY constraint.

Let’s see how the ISBN column of the Book table is referenced by the Bookstore table.

ISBN	LocationInStore	PriceUSD
0-8244-8247-6	Regal A	20.99
0-4506-2393-9	Storage	30.59
0-6076-7451-2	Regal B	25.79

The ISBN column of the Bookstore table references the ISBN column of the Book table – these columns are related to each other.

Now we should define what happens if an ISBN column value in the Book table (i.e. the primary table) is updated or removed. Let’s see what options PostgreSQL provides.

The options for update/delete constraint actions are:

• no action (none) – When the referenced row of the primary table is updated/deleted, an error is raised about foreign key constraint violation. It is similar to restrict, except that no action can be deferred to be checked at a later time.
• restrict – When the referenced row of the primary table is updated/deleted, the relevant row in the foreign table is not updated/deleted and an error is raised. Unlike no action, restrict cannot be deferred.
• cascade – When the referenced row of the primary table is updated/deleted, the relevant row in the foreign table is also updated/deleted.
• set null – When the referenced row of the primary table is updated/deleted, the row in the foreign table is set to NULL.
• set default – When the referenced row of the primary table is updated/deleted, the row in the foreign table is set to its default value. Note that a default value must be available for the foreign key column(s).

PostgreSQL constraints can be DEFERRABLE. If this option is set to Yes, then they can be set to INITIALLY DEFERRED.

Let’s take a closer look at these options.

DEFERRABLE vs. NOT DEFERRABLE:

• DEFERRABLE – If the constraint is deferrable, the check on this constraint can be postponed until the end of the transaction; you can modify the data within the transaction so that they meet the constraint at the end of the transaction.
• NOT DEFERRABLE – If the constraint is not deferrable, the check action must be performed immediately after every command and cannot be postponed. The constraint is enforced immediately.

INITIALLY DEFERRED vs. INITIALLY IMMEDIATE:

The following options are applicable only if the constraint is marked as DEFERRABLE.

• INITIALLY DEFERRED – The constraint is checked after each transaction; hence, the check is postponed until the transaction finishes.
• INITIALLY IMMEDIATE – The constraint is checked after each statement, similar to the NOT DEFERRABLE option.

How to Model the FOREIGN KEY Constraint in Vertabelo

Setting up a FOREIGN KEY constraint in Vertabelo first requires that the two tables are linked. To do that, we choose the (4) Add new reference icon from the toolbar and connect the tables, starting from the primary table and drawing a line to the foreign table.

The name of the FOREIGN KEY constraint is automatically generated by Vertabelo, but we can change it if we want. We should then choose the column(s) from the primary and foreign tables to be used for creating the link between these tables.

Next, we should decide on the update/delete constraint action – described above – and choose from the available options, as shown below.

You can set the DEFERRABLE and INITIALLY DEFERRED options – described above – in the Additional properties section of the Reference Properties panel in Vertabelo.

Once all the options for the FOREIGN KEY constraint are set, you can get the SQL code to run it in your database and create the foreign key.

The FOREIGN KEY constraint is added by using the ALTER TABLE statement on the foreign table. As you can see, by default the constraint is NOT DEFERRABLE and INITIALLY IMMEDIATE.

NOT NULL Constraint in PostgreSQL

The NOT NULL constraint ensures that the column implementing it will never contain a NULL. In other words, it must always have a value.

Let’s look at the Product table to see sample usage of the NOT NULL constraint.

ProductId	ProductName	QuantityInStock
1	Notebook	100
2	Pen	250
3	Pencil	300

It is clear that the ProductId column is the primary key for the Product table. We want to always know the name that is assigned to each particular product ID; hence, the ProductName column should implement the NOT NULL constraint. We could allow the QuantityInStock column to store NULLs, e.g. when the product is just added to the database and is not yet in stock.

How to Model the NOT NULL Constraint in Vertabelo

As you might have noticed in the PRIMARY KEY Constraint section, in Vertabelo all columns implement  NOT NULL by default. To make the column nullable, tick the N checkbox in the Columns section, as shown below.

The NOT NULL constraint is created with the column definition, as shown below.

The QuantityInStock column has the NULL keyword in its column definition.

The NOT NULL constraint in PostgreSQL does not have additional database-specific features.

UNIQUE Constraint in PostgreSQL

The UNIQUE constraint does not allow duplicate values in the column that implements it. It can be implemented on one or more columns.

Let’s look at the Departments table, which stores information on the departments of different companies.

Id	DepartmentId	DepartmentName	CompanyName	NumberOfEmployees
1	A34F	HR	Tech Zone	20
2	G56J	IT	Tech Zone	30
3	K87J	Sales	Coffee Corner	10
4	M45B	HR	Curious Eye	15

The DepartmentId column must store unique values to uniquely identify the departments. Alternatively, we could use the combination of the DepartmentName and CompanyName columns and implement the UNIQUE constraint on this combination.

In PostgreSQL, we can implement the UNIQUE constraint as DEFERRABLE or NOT DEFERRABLE. If we choose DEFERRABLE, we can set the constraint as INITIALLY DEFERRED or INITIALLY IMMEDIATE. These options are explained in detail in the FOREIGN KEY constraint section of this article.

PostgreSQL’s UNIQUE constraint also allows us to select the tablespace where the index associated with this UNIQUE constraint will be created. This is done by adding the USING INDEX TABLESPACE clause and providing the tablespace name right after it, as it is shown in the generated SQL code in How to Model the UNIQUE constraint below. If the USING INDEX TABLESPACE clause is not included, the default tablespace is used.

There is also a WITH clause offered by PostgreSQL for the UNIQUE constraint. The WITH clause is optional and can be used to specify storage parameters for a table or index.

How to Model the UNIQUE Constraint in Vertabelo

If you want to implement the UNIQUE constraint on a table, you should go to the Alternate (unique) keys section in Vertabelo, click on Add key, and add the column(s) that must be unique.

In the Alternate (unique) keys section, you can also set the DEFERRABLE, INITIALLY DEFERRED, INDEX TABLESPACE, and WITH options.

Here, we allow the checks for the UNIQUE constraint to be deferred until the transaction is finished. The index created by the UNIQUE constraint is stored within the default_tablespace. And the storage option specified for this table is fillfactor(90), i.e. the rows inserted into this table can fill the table pages up to 90%.

The UNIQUE constraint is generated within the CREATE TABLE statement.

DEFAULT Constraint in PostgreSQL

Very often we need a default value for a column. This default value is used when there is no value provided for this column in the INSERT statement. We can set the default value for a column using the DEFAULT constraint.

Let’s look at the Employees table.

EmployeeId	FirstName	LastName	StartDate
1	Andrea	Thompson	01-01-2010
2	David	Brown	10-05-2015
2	Claire	White	15-07-2014

We could set the default value for the StartDate column to be today’s date. This is assuming that the employee is added to the database on the day their employment starts.

How to Model the DEFAULT Constraint in Vertabelo

The default value for a column can be set in the Columns section, as shown below.

The DEFAULT constraint is generated with the column definition in the CREATE TABLE statement.

CHECK Constraint in PostgreSQL

The CHECK constraint is used to validate any custom condition against the values in the column(s) of newly inserted rows.

Let’s look at the PubCustomers table.

Id	Nickname	Age	VisitsMade
23	Kerry	21	5
24	Larry	23	7
25	Penny	24	6

This particular pub allows customers who are 18 or older, so we could implement the CHECK constraint on the Age column to limit its values to equal to or greater than 18.

The CHECK constraint can also be implemented on more than one column. We could create a CHECK constraint that validates the Age column values to be equal to or greater than 18 and the VisitsMade column values to be 5 or more.

How to Model the CHECK Constraint in Vertabelo

A CHECK constraint that includes only one column is set the same way as the DEFAULT constraint.

The generated SQL code includes the CHECK constraint in the column definition within the CREATE TABLE statement.

To set a CHECK constraint on more than one column, go to the Checks section and edit the Check expression, as shown below. Note that in the Checks section you can define the name of the constraint, if you need to.

The generated SQL code includes this CHECK constraint in the CREATE TABLE statement.

The CHECK constraint in PostgreSQL can be added to a table using the ALTER TABLE ADD NOT VALID option. This option allows us to add the CHECK constraint to the table immediately, without validating its current data against this constraint. A database check that validates the compliance of all the rows of this table with the CHECK constraint can be performed at a later time.

If you need a more detailed explanation of database constraints and their definitions in Vertabelo, check out the article Database Constraints: What They Are and How to Define Them in Vertabelo.

Indexes in a PostgreSQL Database

Indexes are a widely used data structure in various database engines. By using indexes properly, you can improve your database performance and make data retrieval operations faster; however, you should note that additional storage space is necessary for indexes’ data structures. And it’s also good to note that you can degrade database performance by the poor usage of indexes.

Let’s look at the sample usage of indexes and the various options offered by PostgreSQL.

In Vertabelo, indexes can be created in the Indexes section, as shown below.

To add an index on a particular table and column:

1. Go to the Indexes section.
2. Click on Add Index.
3. Expand the newly created index.
4. Choose the column(s) to be included in the index.
5. Column order can be set to either ASC (ascending) or DESC (descending).

Postgres makes the following options available for indexes:

• UNIQUE – By using CREATE UNIQUE INDEX (instead of CREATE INDEX), we ensure that database checks are performed at the time of index creation and at every data insert so that there are no duplicate values.
• USING – This is essentially the index type. By default, PostgreSQL uses btree (associated with the b-tree data structure). The btree index type ensures a balanced amount of data among the tree branches. It can be used for equality and range queries and can operate on all data types, including NULLs. There are other index types available, such as hash, gist (generalized search tree), spgist, gin (generalized inverted index), and brin (block range index).
• TABLESPACE – This specifies the tablespace where the index will be created.
• WITH – It specifies the storage option, as mentioned before.
• WHERE – It specifies the table rows where the index is created. For example, if we add Age>21 in the WHERE clause, the index will be created only for rows where the Age column contains a value of 22 or greater. This is called a partial index, as it is declared on a subset of the table’s rows.

In the generated SQL code, the CREATE INDEX statement follows the CREATE TABLE statement.

In the generated SQL code, the CREATE INDEX statement follows the CREATE TABLE statement.

Here, an index is created for the PubCustomers table on the Nickname column in ascending order.

And here’s another example with all the options used:

• The btree method is used to create the index.
• The index is created in the default_tablespace.
• fillfactor(90) is used as the storage option.
• The index is created using rows where the Age column values are equal to or greater than 21.

Although indexes are not database constraints, they go hand in hand with database design. Hence, it is essential to know what indexes are and how to use them to enhance your database performance.

PostgreSQL Constraints and Indexes Are Key to Database Design

Database constraints are a very essential concept in database design and modeling. Understanding PostgreSQL constraints and how to model them in Vertabelo enables you to create your database design, generate SQL DDL codes, and bring your model to life by executing the codes in the database.

PostgreSQL offers some additional options for constraints, such as DEFERRABLE and INITIALLY DEFERRED. PostgreSQL is a powerful, open-source, and well-known database engine that’s worth investing your time to learn.