Database Constraints: What They Are and How to Define Them in Vertabelo

Database constraints help us keep our data clean and orderly. Let’s look at the most common database constraints and how to conveniently define them in Vertabelo.

It’s a common practice to set rules for the data in a database. Thanks to these rules, you can avoid incorrect data in a column, e.g. a text string in an Age column or a NULL in a Name column.

In this article, we’ll dive into SQL’s database constraints: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK. Each constraint will get its own section, which will include:

• A definition of the constraint.
• An example.
• How to use the constraint in Vertabelo:
  • Defining the constraint on a column or set of columns.
  • Generating a SQL script that contains the constraint.

Let’s get started!

Primary Key Constraint

Definition and Example

The PRIMARY KEY constraint is defined on one or more columns in a table. It indicates that this column (or set of columns) must uniquely identify each row of the table. The columns that are part of the PRIMARY KEY must comply with the UNIQUE and NOT NULL constraints at the same time, i.e. the column(s) cannot contain duplicate or null values.

Let’s look at the Employees table.

EmployeeId	FirstName	LastName	Department	Position
123654	Amy	Thomson	IT	Programmer
876345	David	Black	Sales	Sales Representative
987345	Bart	Anderson	IT	Manager

In this case, we want each employee to have a unique EmployeeId value. Also, we want to avoid any employee having no EmployeeId. Hence, we must impose the PRIMARY KEY constraint on the EmployeeId column, to comply with the business rules set for this table.

Usage in Vertabelo

How to Set Up a Primary Key Constraint in Vertabelo

To make a column implement the PRIMARY KEY constraint in Vertabelo, you just need to tick the checkbox under PK, as shown in the image below:

Also, you may notice the column that implements the PRIMARY KEY constraint is marked with PK on the diagram.

Consider a situation where the company wants to have employee IDs that are unique within each department – i.e. the employee IDs can repeat within the company but not within the department. In such a case, you should create a composite PRIMARY KEY constraint using the EmployeeId and Department columns, as shown below:

In the article on How to Create a Multicolumn Primary Key, you can find more about multicolumn primary keys in Vertabelo.

What Does Vertabelo Generate in the SQL Script for Primary Key Constraints?

After creating a table in Vertabelo (as shown above), you can see the SQL code defining your performed actions by clicking the SQL Preview button.

The SQL DDL code generated by the Vertabelo data modeler can be used to create this Employees table according to your design.

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

Foreign Key Constraint

Definition and Example

The FOREIGN KEY constraint is defined on one or more columns in a table as a reference to the PRIMARY KEY column(s) of another table. The FOREIGN KEY constraint creates a link or reference between the tables.

Let’s look at the EmployeeDetails table, which has a FOREIGN KEY that references the EmployeeId column of the Employees table from the example above.

Id	EmployeeId	Phone	Email
1	123654	0456234789	amy.t@sample.com
2	876345	0987456234	david.b@sample.com
3	987345	0567345123	bart.a@sample.com

The EmployeeDetails table can also have its own PRIMARY KEY column – in this case, the Id column.

Usage in Vertabelo

How to Set Up a Foreign Key Constraint in Vertabelo

Let’s go through the steps on how to set up the FOREIGN KEY constraint between the Employees and EmployeeDetails tables in the Vertabelo data modeler.

First, we must create the EmployeeDetails table.

The next step is to create a reference by changing the selected icon from (1) Select into (4) Add new reference in the toolbar, as shown below.

Now, draw a line between the tables you want to connect. The table at which you start drawing the line is the primary table (here, Employees) and the table at which you end the line is the foreign table (EmployeeDetails). Next, specify the following parameters in the Properties tab:

• Name – The name of the FOREIGN KEY
• Cardinality – Specifies how many records in the foreign table can be related to one record in the primary table. (I’ll describe cardinality in greater detail below.)
• Primary and Foreign tables – The foreign table references the column(s) of the primary table; you can list all these columns here.
• Swap reference button – Useful when you want to swap the primary and foreign tables.
• Update constraint action – Choose an action for the database to follow on data updates in the primary or foreign tables (described in greater detail below).
• Delete constraint action – Choose an action for the database to follow on data removal in the primary or foreign tables (described in greater detail below).

For each record in the Employees table, there must be exactly one record in the EmployeeDetails table. Hence, the cardinality is set to 1..1. For more information on one-to-one relationships, check out What Is a One-to-One Relationship in a Database?. You could of course use other cardinality values, such as one-to-many; find out more in What Is a One-to-Many Relationship in a Database?.

After setting the cardinality, you can choose the particular columns that you want to connect these tables. In our example, the connecting columns are the EmployeeId columns from both tables.

As for cardinality, the available values are:

Cardinality values dictate how many records of the foreign table can be related to one record of the primary table. We could choose cardinality value 0..1, which would mean that for each record of the Employees table, there is zero or one record in the EmployeeDetails table.

When creating the FOREIGN KEY constraint, you also define the actions that are performed on update or on delete of the data from related tables. In Vertabelo, you just need to choose the desired option from the drop-down menu:

The following standard options are available:

• restrict – The update/delete operation is rejected for the primary (parent) table.
• cascade – When the row is updated/deleted in the primary (parent) table, it causes the row to be updated/deleted in the foreign (child) table as well.
• set null – The update/delete operation on the primary table causes the associated values in the foreign table to be set to NULL; here, the assumption is that the FOREIGN KEY column(s) allow NULL values.
• set default – The update/delete operation on the primary table causes the associated values in the foreign table to be set to their default values (which are defined for each column); here, the assumption is that the FOREIGN KEY column(s) have the default values defined.

What Does Vertabelo Generate in the SQL Script for Foreign Key Constraints?

The SQL code generated for the creation of the EmployeeDetails table looks like this:

After the creation of the EmployeeDetails table, we added the FOREIGN KEY constraint. It resulted in the following SQL code being generated:

The ALTER TABLE statement is used to add the FOREIGN KEY constraint on the EmployeeId column of the EmployeeDetails table. It references the EmployeeId column of the Employees table.

Not Null Constraint

Definition and Example

The NOT NULL constraint – as its name indicates – prevents the column that implements it from storing null values. If we want a specific column to always have a value, the NOT NULL constraint is the way to go.

Let’s look at the Person table:

Id	FirstName	LastName	SocialSecurityNumber	PhoneNumber
1	Kyle	Andersen	345876567678456	0654876345
2	Bryan	Black	678345876567345	null
3	Kate	Thompson	908456207895437	null

Each person must have a social security number. Hence, the SocialSecurityNumber column could well use the NOT NULL constraint, thus avoiding any person having no SSN.

Usage in Vertabelo

How to Set Up the Not Null Constraint in Vertabelo

As you might have noticed in the previous examples, all the columns in Vertabelo by default implement the NOT NULL constraint. If you want the column to be nullable, tick the checkbox under N:

The Person table allows the PhoneNumber column to store null values, as not every person must have a phone number.

What Does Vertabelo Generate in the SQL Script for Not Null Constraints?

The NOT NULL constraint is generated within the CREATE TABLE statement, in the definition of the column that implements it:

Every column of the Person table (except for PhoneNumber)implements the NOT NULL constraint.

Unique Constraint

Definition and Example

The UNIQUE constraint prohibits the column that implements it from storing duplicate values. One UNIQUE constraint can also contain multiple columns; in such a case, the combination of these columns must be unique. If you want a specific column value to be unique for each record in the table, use the UNIQUE constraint.

Let’s look at the Person table again.

Id	FirstName	LastName	SocialSecurityNumber	PhoneNumber
1	Kyle	Andersen	345876567678456	0654876345
2	Bryan	Black	678345876567345	null
3	Kate	Thompson	908456207895437	null

Each person must have a social security number. Hence, the SocialSecurityNumber column could well use the NOT NULL constraint, as mentioned in the example above. However, the social security number must also be unique to each person. So we could use the UNIQUE constraint to ensure the uniqueness of each value in the SocialSecurityNumber column.

Usage in Vertabelo

How to Set Up  a Unique Constraint in Vertabelo

To make a column implement the UNIQUE constraint in Vertabelo, the alternate (unique) key should be added, as shown below. Please note that we are no longer in the Columns section; we’ve moved down to the Alternate (unique) keys section of TABLE PROPERTIES.

We added a key under Alternate (unique) keys. The name of this UNIQUE constraint is UniqueSSN and the column that implements it is SocialSecurityNumber.

Let’s see how to set up a multi-column UNIQUE constraint.

In the columns list (marked in red), there are two columns: LastName and SocialSecurityNumber. The combination of these columns must be unique; that is, the columns themselves can store duplicate values as long as the combination of these columns is always unique.

What Does Vertabelo Generate in the SQL Script for Unique Constraints?

The UNIQUE constraint is generated within the CREATE TABLE statement:

You could also declare more than one UNIQUE constraint within a table or make a UNIQUE constraint contain more than one column – in that case, the combination of these columns must be unique.

The SQL code generated for the multi-column UNIQUE constraint is as follows:

As you might have noticed, the difference in the SQL code between the single- and multi-column UNIQUE constraint is just the list of columns present in the UNIQUE constraint definition.

Default Constraint

Definition and Example

The DEFAULT constraint can be used to assign a default value to a column. This default value would be used for that column when no value is provided for it during data input.

Let’s look at the Users table:

Id	Username	Status
456	ally	active
345	missy	inactive
876	lenny	active

Suppose a DEFAULT constraint is placed on the Status column. If a user is added to the Users table without any Status value assigned, we could assume that the user is inactive until their explicit activation. Hence, we could use the DEFAULT constraint on the Status column, with the default value being inactive.

Usage in Vertabelo

How to Set Up a Default Constraint in Vertabelo

The DEFAULT constraint is implemented on a column by specifying its default value. The default value for the column can be defined under Columns by expanding the column:

Please note that, in this example, the default value should be inputted here within single quotes because the Status column is of the VARCHAR data type.

What Does Vertabelo Generate in the SQL Script for Default Constraints?

The DEFAULT constraint is generated within the CREATE TABLE statement in the relevant column definition:

Here, the default value inactive is set for the Status column.

Check Constraint

Definition and Example

The CHECK constraint allows us to impose any rule on the table’s column(s). For example, we could use the CHECK constraint to restrict the Age column to values between 25 and 40 or we could make the CountryCode column have only specific countries’ codes. If a user inputs a value that does not comply with the rules set by the CHECK constraint, they’ll get an error message.

Let’s look at the Clients table below.

Id	FirstName	LastName	Age
1	Kyle	Andersen	26
2	Bryan	Black	39
3	Kate	Thompson	34

The Clients table implements the CHECK constraint on the Age column. The Age column is restricted to contain only the values between 25 and 40. If anyone tries to input a client of age below 25 or above 40, it will result in an error.

Usage in Vertabelo

How to Set Up a Check Constraint in Vertabelo

The CHECK constraint for a single column is set in Vertabelo the same way as the DEFAULT constraint. Expand the column under Columns, as shown below, and input the expression for the CHECK constraint.

You might also want to define a CHECK constraint that involves multiple table columns. To do this, look under Checks, where you can define the expression for the CHECK constraint.

What Does Vertabelo Generate in the SQL Script for Check Constraints?

The CHECK constraint for a single column is generated within the CREATE TABLE statement, in the relevant column definition.

CHECK constraints for a table or for multiple columns are also generated within the CREATE TABLE statement.

What’s Next with Database Constraints?

SQL database constraints are essential for database modelers or architects. Because of their straightforwardness, it is easy to grasp the concepts related to database constraints; this also helps us understand the database in general.

Vertabelo allows you to construct your database model and present it in a clear manner to an audience of any level of technical skill. Assuming you understand data modeling, the construction of a database model in Vertabelo is easy and intuitive. For more info on setting up database constraints and creating your database model in Vertabelo, please see our article on How to Create Physical Diagrams in Vertabelo.

Also, once you define your tables with specific database constraints in Vertabelo, you can export them as DDL scripts to easily create the tables in your database. Check out our article on How to Generate a SQL DDL Script from an ER Diagram in Vertabelo to learn more!