Constraints in MySQL and How to Model Them in Vertabelo

Before you do anything, you start with designing the database. It includes columns, tables, and relationships among them, as well as constraints. Database constraints play a crucial role in that they let you prevent any undesired data from being stored in a column or table. Let’s see how to make use of database constraints in MySQL.

In this article, we’ll go through all database constraints available in a MySQL database:

• Primary key.
• Foreign key.
• NOT NULL.
• UNIQUE.
• DEFAULT.
• CHECK.

I am going to give you a brief overview with examples for each constraint. We’ll also see how to model them in Vertabelo to bring our database design to life. At the end, we’ll touch on the indexes available in the MySQL database and how to model them in Vertabelo.

Get Started With MySQL Database Design in Vertabelo

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

1. Choose Create new document from the toolbar.

2. Click on Create next to the Physical database model.

3. Provide the name of the model, choose the database engine MySQL 5.x, and click the Start Modeling

Check out this article on How to Create Physical Diagrams in Vertabelo to find out more about creating documents containing physical diagrams.

Let’s jump into the topic of database constraints available in MySQL!

Before We Start

Let’s look at the tables we will use throughout this article. We will go through a number of examples for each database constraint in MySQL to get a better understanding.

The main table in our database is the Employees table. It stores basic information about the employees. The EmployeeDetails table contains the details about each employee, and it is connected to the Employees table by a 1-to-1 relationship.

The Departments table provides a list of all departments within the company. It is connected with the EmployeeDetails table by a 1-to-many relationship, as one department can have many employees.

Our company gives each employee the opportunity to lease one or more cars during the employment period. Hence, the Employees table is connected with the EmployeeCar table by a 1-to-zero or 1-to-many relationship. Furthermore, the EmployeeCar table is the intermediary table between the tables Employees and Cars.

A side note:

As you may notice, the EmployeeDetails table contains the LeaseCar column which stores a Boolean. It indicates whether a given employee has a leased car (true) or not (false). It would be convenient to have some MySQL stored procedure that would check the EmployeeCar table for data and update the LeaseCar column of the EmployeeDetails table accordingly.

Now that we have created our MySQL database design in Vertabelo, let’s see what database constraints we need to define to avoid storing undesired data.

The Primary Key Constraint

The primary key constraint is a standard feature of every relational database engine. It can consist of one or more columns. The primary key column, or a set of columns, uniquely identifies each row of the table.

Let’s take the Employees table as an example. There may be multiple employees within the company with the same first name and/or the same last name. This is why it is good to have a custom unique identifier for each employee, such as the employee ID.

EmpId	FirstName	LastName	PhoneNo	Email
2376	Alissa	Jefferson	045634587645	a.jefferson@mail.com
2377	Bob	Weber	098745687642	joel.weber@mail.com
2378	Bob	Jefferson	096745625478	bob.w@mail.com

The EmpId column uniquely identifies each employee; it plays a role of the primary key for the Employees table.

How to Model a Primary Key Constraint in Vertabelo

It couldn’t be easier to implement a primary key constraint in Vertabelo. You just need to check the PK checkbox next to the column and voilà! The column becomes the primary key.

Let’s see exactly how it works in Vertabelo.

As mentioned earlier, a primary key can also consist of more than one column as it does in the EmployeeCar table.

In the Primary key section in Vertabelo, you can also define the name for your primary key constraint and add or remove columns that make up the constraint.

Besides these features, Vertabelo can generate the SQL code for you to create the table with all the columns and constraints just by running it. By choosing the table of interest and clicking the SQL preview button, you get the code necessary to bring your design to life.

The primary key constraint definition is generated within the CREATE TABLE statement. To get more insight on how to generate an SQL script in Vertabelo, check out this article.

The Foreign Key Constraint

The foreign key constraint is another standard feature in relational databases, as it is a part of the database normalization rules together with the primary key constraint. The foreign key constraint connects data in multiple tables based on one (or more) common column(s). This common column is called the primary column in the primary table of the foreign key relationship; in the foreign table of the foreign key relationship, it is called the foreign column.

The foreign key constraint is present at every step in our database:

• The EmpId column of the Employees table is a foreign key in the EmployeeDetails table (the EmpId column). It is presented in red in the diagram below.
• The DepartmentId column of the Departments table is a foreign key in the EmployeeDetails table (the DepartmentId column). It is presented in green in the diagram below.
• The EmpId column of the Employees table is a foreign key in the EmployeeCar table (the EmpId column). It is presented in blue in the diagram below.
• The CarId column of the Cars table is a foreign key in the EmployeeCar table (the CarId column). It is presented in yellow in the diagram below.

To understand the foreign key constraint, an illustrative example comes in handy.

Let’s consider the Employees and EmployeeDetails tables that are connected by a 1-to-1 relationship. The EmpId column of the Employees table is the foreign key in the EmployeeDetails table.

EmpId	FirstName	LastName	PhoneNo	Email
2376	Alissa	Jefferson	045634587645	a.jefferson@mail.com
2377	Bob	Weber	098745687642	joel.weber@mail.com
2378	Bob	Jefferson	096745625478	bob.w@mail.com

EmpId	SSN	DepartmentId	Salary	LeaseCar
2376	765345987456	1	4000	true
2377	654789654345	1	3500	true
2378	234876456987	2	5000	false

All is good up to now. Next, you might wonder how such a construction behaves if you update or delete rows in the primary (Employees) table.

There is a predefined set of actions in MySQL that can be specified ON UPDATE or ON DELETE of rows for the columns involved in the foreign key constraint.

1. ON UPDATE NO ACTION / ON DELETE NO ACTION

The NO ACTION option is the default option when you do not specify the ON UPDATE or ON DELETE clause. The database rejects the UPDATE or DELETE operation performed on the parent (Employees) table if a related value exists in the child (EmployeeDetails) table.

Example:

If you try to update the row where EmpId=2376 in the Employees table, the database throws an error, because the record with EmpId=2376 is also present in the EmployeeDetails table.

2. ON UPDATE RESTRICT / ON DELETE RESTRICT

The RESTRICT option is equivalent to the NO ACTION option in MySQL. Defining the ON UPDATE or ON DELETE clause using the RESTRICT or NO ACTION option is the same as not defining it at all.

3. ON UPDATE CASCADE / ON DELETE CASCADE

As the name implies, it cascades the changes made in the parent table down to the child table. Any UPDATE or DELETE operation on a row in the parent table results in the same operation performed automatically on the matching row in the child table.

Example:

If the employee with EmpId=2378 no longer works for our company, we need to remove this record from the database. To avoid going into each table and removing it manually, we can make use of the ON DELETE CASCADE option. After removing the record with EmpId=2378 from the Employees table, the record for the same employee is automatically removed from the EmployeeDetails table.

4. ON UPDATE SET NULL / ON DELETE SET NULL

This option sets the value of the matching row in the child table to NULL when the corresponding value in the parent table is updated or deleted.

Example:

If we update the EmpId column in the Employees table for Alissa Jefferson from 2376 to 2375, then the Employees table stores the newly updated value (2375), while EmpId of the row that used to correspond in the EmployeeDetails table under the old value stores NULL.

5. ON UPDATE SET DEFAULT / ON DELETE SET DEFAULT

Similar to the SET NULL option, instead of setting the value in the child table to NULL on UPDATE or DELETE of the matching value in the parent table, here we set the value in the child table to its DEFAULT value. Please note that, to use the SET DEFAULT option, we must first define the DEFAULT value for the column in the child table (see the section below on the DEFAULT constraint).

Example:

Let’s assume that we have set the default value for the EmpId column of the EmployeeDetails table to 0. If we remove the row where EmpId=2377 from the Employees table, the EmpId for Bob Weber in the EmployeeDetails table becomes 0.

The MySQL database engine has additional options for multi-column foreign key constraints. The MATCH clause governs the rules for the NULL values in this case.

Let’s look at an example of a multi-column foreign key.

Here, the Books table is the primary table and the BookTypes table is the foreign table. The Title and Author columns of the Books table make up the multi-column foreign key in the BookTypes table.

The MATCH clause offers 3 options for the multi-column foreign key:

1. MATCH FULL

This option allows all columns in the foreign key (here, the Title and Author columns of the BookTypes table) to be either all NULL (i.e., Title is NULL and Author is NULL) or none of them NULL (i.e., Title is not NULL and Author is not NULL). If, for any given row in the BookTypes table, all foreign key columns are NULL, then the row is not required to have a match in the Books table.

2. MATCH PARTIAL

This option allows all columns in the foreign key (here, the Title and Author columns of the BookTypes table) to be either all NULL (i.e., Title is NULL and Author is NULL), none of them NULL (i.e., Title is not NULL and Author is not NULL), or partially NULL (i.e., Title is NULL and Author is not NULL, or vice versa). If, for a given row in the BookTypes table, all foreign key columns are NULL, then the row is not required to have a match in the Books table. In contrast, if for any given row in the BookTypes table, one or more foreign key columns are non-NULL, then there must be a row in the Books table that matches by all non-NULL values.

3. MATCH SIMPLE

This option allows all columns in the foreign key (here, the Title and Author columns of the BookTypes table) to be either all NULL (i.e., Title is NULL and Author is NULL), none of them NULL (i.e., Title is not NULL and Author is not NULL), or partially NULL (i.e., Title is NULL and Author is not NULL, or vice versa). If, for any given row in the BookTypes table, any column in the foreign key is NULL, then the row is not required to have a match in the Books table.

Although the MATCH clause is allowed in MySQL, it is not recommended. The reason is when the MATCH clause is used in the CREATE TABLE statement, the ON UPDATE and ON DELETE clauses are ignored during execution.

Now that we have an idea of how to use the foreign key constraint in MySQL, let’s look at how we can model it in Vertabelo.

How to Model a Foreign Key Constraint in Vertabelo

To implement a foreign key constraint in Vertabelo, you should first change the cursor in the toolbar from Select to Add new reference as shown below.

The next step is to draw a line from the primary table, i.e., the Employees table, to the foreign table, i.e., the EmployeeDetails table.

Now, in the Reference Properties tab, you can customize the connection by choosing the columns, naming the foreign key constraint, selecting ON UPDATE/ON DELETE actions, choosing the cardinality, and setting the MATCH option.

The SQL code for the foreign key constraint is generated after the CREATE TABLE statement using the ALTER TABLE statement.

The NOT NULL Constraint

The NOT NULL constraint is very straightforward. When it is implemented on a column or on a set of columns, the NULL values are not allowed in the column(s).

Let’s look at an example in our company’s database. The DepartmentId column of the EmployeeDetails table should not be NULL, because every employee should belong to a department.

This is allowed…

EmpId	SSN	DepartmentId	Salary	LeaseCar
2376	765345987456	1	4000	true
2377	654789654345	1	3500	true
2378	234876456987	2	5000	false

…and this is not allowed.

EmpId	SSN	DepartmentId	Salary	LeaseCar
2376	765345987456	NULL	4000	true
2377	654789654345	1	3500	true
2378	234876456987	2	5000	false

Let’s see how to define a NOT NULL constraint in Vertabelo.

How to Model a NOT NULL Constraint in Vertabelo

To define a NOT NULL constraint in Vertabelo – just do nothing! As you might have noticed, the NOT NULL constraint is the default for every column (see the SQL code generation earlier in the section on How to Model a Primary Key Constraint in Vertabelo).

Now you may ask: what if you want a column to be nullable? You just check the N checkbox next to the column.

We could decide to make the Email column of the Departments table nullable. If a new department is created, at first it may not have its own email address. It is plausible to allow this column to store NULL values.

The Email column definition includes NULL. This means it can store NULL values in contrast to the other two columns.

So, that was quite easy. Let’s go to the next database constraint!

The UNIQUE Constraint

The UNIQUE constraint implemented on a column or a set of columns ensures that the column(s) can store only unique values.

To give an example, let’s consider the EmployeeDetails table. The Social Security number (SSN) is unique to every person, so it should be unique to every employee. Hence, the SSN column must store only unique values.

This is allowed…

EmpId	SSN	DepartmentId	Salary	LeaseCar
2376	345987456	1	4000	true
2377	789654345	1	3500	true
2378	876456987	2	5000	false

…and this is not allowed.

EmpId	SSN	DepartmentId	Salary	LeaseCar
2376	345987456	NULL	4000	true
2377	789654345	1	3500	true
2378	789654345	2	5000	false

The MySQL database offers additional features with the UNIQUE constraint. In the following section on how to model a UNIQUE constraint in Vertabelo, you will see the generated SQL code; a UNIQUE constraint is implemented there using the following syntax within the CREATE TABLE statement:

UNIQUE INDEX EmployeeDetails_SSN_Unique (SSN)

The UNIQUE constraint is also an index created on the SSN column of the EmployeeDetails table. This is why we can declare its type via a USING clause that comes with the UNIQUE constraint. It offers two types of indexes.

1. USING BTREE

The BTREE index characteristic stores its data in a tree structure that allows fast access, as the data is stored in a balanced manner among the tree branches. This type of index is recommended for column comparisons that use operators such as =, >, <, >=, <=, BETWEEN, and LIKE.

2. USING HASH

In contrast, the HASH index characteristic is used only for equality comparisons with operators such as = and <=>. These are the so-called key-value stores that lookup a single value and check whether it is or is not equal to the value on the right-hand side.

There is one more option related to indexes offered by MySQL. You can specify the size for index key blocks using the KEY_BLOCK_SIZE parameter that follows the USING clause. This size is specified in bytes and is treated as a hint by the database engine.

How to Model a UNIQUE Constraint in Vertabelo

To implement a UNIQUE constraint in Vertabelo, you need to create a unique key in the Alternate (unique) keys section. You specify the name of the UNIQUE constraint and choose the column(s) that implement it.

The SQL code is generated, with the UNIQUE constraint definition within the CREATE TABLE statement.

In this same section, you can specify a USING clause and the KEY_BLOCK_SIZE parameter as follows.

Now, the generated SQL code is below.

The UNIQUE constraint is generated within the CREATE TABLE statement.

The DEFAULT Constraint

The DEFAULT constraint allows you to set the DEFAULT value for a column. This DEFAULT value is used when the inserted row does not have any value for the column.

Let’s look at the EmployeeDetails table. The LeaseCar column contains information on whether an employee has one or more leased cars (true) or not (false). An employee who has just joined the company does not have any leased cars at first, therefore by default, the value for the LeaseCar column should be set to false.

Now, let’s insert a row into the EmployeeDetails table without providing the value for the LeaseCar column:

INSERT INTO EmployeeDetails
 VALUES (2380, ‘456987174068’, 1, 5000);

Now, the EmployeeDetails table stores 4 rows after the INSERT statement above.

EmpId	SSN	DepartmentId	Salary	LeaseCar
2376	765345987456	1	4000	true
2377	654789654345	1	3500	true
2378	234876456987	2	5000	false
2380	456987174068	1	5000	false

The LeaseCar column contains false by default.

How to Model a DEFAULT Constraint in Vertabelo

To set the DEFAULT value for a column in Vertabelo, go to the Columns section and expand the column of interest. Next, fill the Default value textbox with the default value for that column.

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

Let’s now look at the last database constraint. After that, we’ll move on to database indexes.

The CHECK Constraint

The CHECK constraint is the most flexible of all constraints. Why? With a CHECK constraint, you can implement any condition you can think of, and the column on which it is implemented must obey this condition.

The CHECK constraint can be implemented on one or more columns. Let’s go through each of these cases.

To implement a CHECK constraint on a single column, follow the column definition with the definition of the constraint as we will show below. Let’s consider the EmployeeCar table and its LeaseUntil column that stores DATE type values.

The LeaseUntil column contains the date on which the lease period ends. We want to ensure that this date is in the future. To do this, we can follow the column definition with a CHECK clause.

LeaseUntil date NOT NULL CHECK (LeaseUntil >= CURDATE())

To implement a CHECK constraint on multiple columns, we must include the CHECK clause inside the CREATE TABLE statement after defining all the columns. Let’s consider the EmployeeCar table again.

The EmployeeCar table, besides storing the end date of a car lease contract, also stores its start date. We want to ensure that the end date is in the future and the start date is either today or in the past.

CHECK (LeaseUntil >= CURDATE() AND LeaseFrom <= CURDATE())

How to Model a CHECK Constraint in Vertabelo

To define a CHECK constraint on a single column, follow steps similar to those given for the DEFAULT constraint. After expanding the column in the Columns section, you see the Check expression textbox. Fill in the condition the column needs to meet.

As mentioned before, the single-column CHECK constraint in the SQL code goes along with the column definition.

The multi-column CHECK constraint is defined in Vertabelo a little differently. After selecting the table, fill in the CHECK constraint information such as Name and Check expression in the Checks section.

Multi-column CHECK constraints are generated within the CREATE TABLE statement after all the column definitions.

Please note that, to give a name to any of your CHECK constraints, be it single-column or multi-column, you must go to the Checks section.

Let’s Look at Indexes in the MySQL Database

We’ll start with the basics of what a database index is and why you would want to use it.

When you search for a word in an encyclopedia, you do not go page by page to find it. That would be way too much work! Instead, you go to the very end of the book where all the words are listed alphabetically, and each word is associated with the page number on which you can find its definition. That’s the way indexes work in a database!

A database index is a database structure that helps speed up data retrieval. As good as this may sound, there is in fact a price to pay, mostly in the form of storage used to accommodate the index structures.

The MySQL database engine offers various options that can be used while creating an index. Let’s go through some of them.

1. USING

As it was described in the section on the UNIQUE constraint, there are two index characteristics to choose from: BTREE and HASH. An index that uses the BTREE data structure is best for column comparisons, and an index that uses the HASH data structure is best for equality comparisons.

2. INDEX TYPE

There are 3 index types available in MySQL. One of them, the UNIQUE index, was described in detail in the section on the UNIQUE constraint; it enforces the uniqueness of values in a column.

The FULLTEXT index can be used only for columns of type VARCHAR, CHAR, or TEXT. It performs a full-text search against character-based data. In other words, it is then returned only if the full search text matches the data in the column. The SPATIAL index uses the R-tree data structure. It is similar to B-tree; however, B-tree can store only one-dimensional values, whereas R-tree supports multidimensional indexes. You can use a SPATIAL index to store geographical coordinates and other multidimensional data.

3. KEY BLOCK SIZE

This option was also described in the section on the UNIQUE constraint. Using the KEY_BLOCK_SIZE parameter, you can specify the size of the index key blocks. This size is specified in bytes and is treated as a hint by the database engine.

4. WITH PARSER

This option can be used only with the FULLTEXT indexes. If the FULLTEXT index type is chosen and the WITH PARSER option is defined with an argument that is a parser plugin, then this parser plugin is associated with the index to customize the indexing and searching operations.

Let’s see how to define indexes in Vertabelo.

We do all of this in the Indexes section. First, give your index a name. Next, choose a column on which to create the index, and next to it there is a dropdown menu for the order of this index: either ascending (ASC) or descending (DESC). Then, there come all the options described above, such as USING, INDEX TYPE, KEY BLOCK SIZE, and WITH PARSER.

The index definition is generated in the SQL code after the CREATE TABLE statement.

You’re Good to Go With MySQL Constraints!

That is all for database constraints and indexes! You can now design your database in MySQL.

As a database designer (or a database designer to-be), you are well aware of the importance of database constraints and indexes. A well-designed database must be robust and resistant to human errors. By implementing the rules in the form of constraints to be obeyed by the data, you can prevent almost any error from manual inserts of data. And by using database indexes, you ensure fast data retrieval that is so crucial today.

To learn more about each database constraint mentioned in this article, follow this article with another on Database Constraints: What They Are and How to Define Them in Vertabelo.

Good luck! And if you are interested specifically in the PostgreSQL database, here you go!