Common SQL errors for Laravel Developers

Common SQL errors and how to solve them in Laravel

Why write this little guide since Laravel uses Eloquent to talk to the database? The answer is simple, the errors the database returns are SQL errors and if you don't know how to interpret them then your life as a developer will be harder.

Table Of Contents

1. Naming convention is your friend.
2. Always validate your data.
3. The missing table or view AKA SQLSTATE[42S02]:
4. The foreign key issue AKA SQLSTATE[HY000]: General error: 1215
5. The troublesome child AKA ERROR 1451 (23000): Cannot delete or update a parent row:
6. The refusal to add or update AKA ERROR 1452 (23000): Cannot add or update a child row:
7. The irksome null value AKA ERROR 1048 (23000): SQLSTATE[23000]: Integrity constraint violation:
8. The missing default value AKA SQLSTATE[HY000]: General error: 1364 Field doesn't have a default value
9. The truncated value AKA SQLSTATE[22001]: String data, right truncated:
10. The value issue AKA SQLSTATE[HY000]: General error: 1366 Incorrect value:

Naming convention is your friend

Some might not agree with the naming conventions that Laravel uses but by following them your life becomes so much easier. You get a lot of stuff out of the box so to say if you stick to the defaults. They are not that many, so they are pretty easy to remember.

1. All tables should be in plural form.
2. Pivot tables should be singular form of the tables joined with an underscore in alphabetical order.
3. Primary keys should be named id
4. Foreign keys should be named in the singular form of the table the reference and the word id seperated by an underscore.
5. Table names consisting of more than one word should have the words seperated by an underscore.

1. All tables are in plural form

If we look at a simple blog application, we need somewhere to store the username, the blog post and any comments that visitors might make to the post. So how should the tables be named. Well it's quite simple we have the following parts in our application.

• A user
• A blogpost
• A comment

The tables for this should then be.

• users
• posts
• comments

How about when the plural form or the word is bent a little differently than just add in an s to the word. Then we should use the proper grammatical for it.

• delivery would be deliveries

Laravel uses a pluralization library for converting model names to plural form behind the scenes.

2. Pivot tables should be singular form of the tables joined with an underscore in alphabetical order.

A pivot table is just a regular table joining two other tables together via their primary keys. If we look at a book registry application we might have the following tables

• books
• libraries

Now a library has many books and a book can be in many libraries. So to solve this many-to-many relation we use a pivot table. The naming convention is that you take the table names in their singular form and snake case them together in alphabetical order. In our case with books and libraries it would become book_library.

3. Primary keys are named id

A primary key is the unique identifier in a table. It in most cases it's numeric or a UUID. You can use another kind of unique value, but it can cause your database to grow more than necessary when that identifier is used in a relationship with another table. The name for this unique identifier should be id.

4. Foreign keys should be named in the singular form of the table reference and the word id seperated by an underscore.

A foreign key is used to define the relation between two tables. If we take our blog example you add a foreign key to the posts' table to tell the database which user has created the post or as in the book registry example you use both the id from the books table and the libraries' table in your pivot table.

You name the column with the singular form of the table and then an underscore followed by the column name of the primary key so in the posts' table the foreign key linking the post to the user is user_id.

5. Table names consisting of more than one word should have the words seperated by an underscore.

If you have tables that you need more than one word to describe should have both words in the name seperated by an underscore. So if you have a table that contains party planners you name the table party_planners. The model name will also contain both words but without the underscore, PartyPlanner.

Stick to these conventions and most of the problem described later will never occur.

Always validate your data

Validation is not only a way to make sure the user doesn't send you any insecure data for you to store in your database. The second and maybe most important function of validation is to make sure you don't display any nasty SQL error messages in your application. The errors that can't be avoided by sticking to the naming convention will for the most part be avoided with proper validation. There are two forms of validation, and I suggest you use both of them.

• Client side validation
• Server side validation

The client side validation could be as simple as adding required to the form elements that are not optional. This prevents most users from sending the form. You can of course add more complex validations if you like on the client side.

Server side validation should always be present to avoid any unpleasant error messages or inserts of malicious data. You can use either validation in your controller or via a FormRequest.

The missing table or view AKA SQLSTATE[42S02]:

Sometimes the SQL error messages can be a bit cryptic but this one is quite straight forward

1SQLSTATE[42S02]: Base table or view not found: 1146 Table 'laraveldb.books' doesn't exist (SQL: select * from `books`)

This means that either the table or database view does not exist in the database. This line is the kicker here and the one that will tell you what is wrong, Table 'laraveldb.books' doesn't exist. So either there is no table or view called books in our database, the table name is misspelled in your migration, or you have deviated from the standard naming convention. The easiest way to check this is open a connection to your database with an application like SequelPro, TablePlus or HeidiSQL. In this guide I will use the commandline and run the following command.

1SHOW TABLES;

This command gives a list of all tables and database views.

The missing table.

 1mysql> SHOW TABLES;
 2+---------------------+
 3| Tables_in_laraveldb |
 4+---------------------+
 5| failed_jobs         |
 6| migrations          |
 7| password_resets     |
 8| users               |
 9+---------------------+
104 rows in set (0.00 sec)

As you can see there is no table called books so the solution is to create the table with a migration then migrate the database, if you already have a migration for it, it will be created when the migration is run.

1php artisan migrate

Then run the show tables query again and see that the books' table is created.

 1mysql> SHOW TABLES;
 2+---------------------+
 3| Tables_in_laraveldb |
 4+---------------------+
 5| books               |
 6| failed_jobs         |
 7| migrations          |
 8| password_resets     |
 9| users               |
10+---------------------+
115 rows in set (0.00 sec)

The misspelled table name or the Non-conventional table name.

 1mysql> SHOW TABLES;
 2+---------------------+
 3| Tables_in_laraveldb |
 4+---------------------+
 5| book                |
 6| failed_jobs         |
 7| migrations          |
 8| password_resets     |
 9| users               |
10+---------------------+
114 rows in set (0.00 sec)

If you are in development you can update the existing migration and name the table correctly and run

Caution this will delete all you data.

1php artisan migrate:fresh

Or you can create a new migration and rename the table.

There are ways to tell Laravel that you use another table name than the one the naming convention dictates you should use, but I recommend sticking with the defaults.

The foreign key issue AKA SQLSTATE[HY000]: General error: 1215

This error occurs when you run your migrations, and you have a foreign key that you put a constraint on.

1SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `books` add constraint
2`books_author_id_foreign` foreign key (`author_id`) references `authors` (`id`))

The error message isn't the best since it can be caused by two different error.

1. The type differs between the primary key of the table the constraint is referencing and the foreign key.
2. The order the tables are migrated in.

We start with the wrong column type.

Our authors table

1Schema::create('authors', function (Blueprint $table) {
2    $table->id();
3    $table->string('name');
4    $table->timestamps();
5});

And our books table

1Schema::create('books', function (Blueprint $table) {
2    $table->id();
3    $table->string('title');
4    $table->unsignedInteger('author_id');
5    $table->timestamps();
6    $table->foreign('author_id')->references('id')->on('authors');
7});

As you can see in the first migration we create an id in the authors table, this id is of the type unsigned big integer since the release of Laravel 7.

We create the foreign key in the second migration, and we tell it to use the type unsigned integer but the id column is of the type unsigned big integer as we can see if we run

1DESC users;

in our database

 1+-------------------+---------------------+------+-----+---------+----------------+
 2| Field             | Type                | Null | Key | Default | Extra          |
 3+-------------------+---------------------+------+-----+---------+----------------+
 4| id                | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
 5| name              | varchar(255)        | NO   |     | NULL    |                |
 6| email             | varchar(255)        | NO   | UNI | NULL    |                |
 7| email_verified_at | timestamp           | YES  |     | NULL    |                |
 8| password          | varchar(255)        | NO   |     | NULL    |                |
 9| remember_token    | varchar(100)        | YES  |     | NULL    |                |
10| created_at        | timestamp           | YES  |     | NULL    |                |
11| updated_at        | timestamp           | YES  |     | NULL    |                |
12+-------------------+---------------------+------+-----+---------+----------------+

So the solution to this error is simply to change the type of our foreign key in the books' table migration.

1 Schema::create('books', function (Blueprint $table) {
2    $table->id();
3    $table->string('title');
4    $table->unsignedBigInteger('author_id');
5    $table->timestamps();
6    $table->foreign('author_id')->references('id')->on('authors');
7});

The other issue is in which order the tables are migrated. They are migrated in the order of the filenames. Our two migrations are named.

• 2020_12_24_174025_create_books_table
• 2020_12_24_174111_create_authors_table

Our books' table is created first, and then we add the authors' table so when we are trying to add the foreign key constraint the authors' table does not exist. There are two ways of fixing this.

1. Rename the migration file for the authors table, so it's ran before the books' migration.
2. Put all constraint in a secondary file and run it last.

I, much prefer the first way to do it. However, it can depend on where you are in your development progress.

So we rename the authors_table migration.

• 2020_12_24_174000_create_authors_table
• 2020_12_24_174025_create_books_table

And run migrate refresh command again.

And then we get a successful migration.

1Migrating: 2020_12_24_174000_create_authors_table
2Migrated:  2020_12_24_174000_create_authors_table (17.89ms)
3Migrating: 2020_12_24_174025_create_books_table
4Migrated:  2020_12_24_174025_create_books_table (57.51ms)

Note:

You don't have to use a foreign key constraint, but it's recommended since you can get some cascading effects when you manipulate the data in a table. If you delete a record in one table the database can if you want to delete all child records connected to the id in the table you are deleting from. this brings us to our next error.

The troublesome child AKA ERROR 1451 (23000): Cannot delete or update a parent row:

Now this error has to do with a foreign key constraint in our database.

If we run

1DESC books;

We see this from our previous example

 1mysql> desc books;
 2+------------+---------------------+------+-----+---------+----------------+
 3| Field      | Type                | Null | Key | Default | Extra          |
 4+------------+---------------------+------+-----+---------+----------------+
 5| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
 6| title      | varchar(255)        | NO   |     | NULL    |                |
 7| author_id  | bigint(20) unsigned | NO   | MUL | NULL    |                |
 8| created_at | timestamp           | YES  |     | NULL    |                |
 9| updated_at | timestamp           | YES  |     | NULL    |                |
10+------------+---------------------+------+-----+---------+----------------+
115 rows in set (0.00 sec)

If we look att the author_id we see that we have a value in the key column, MUL. That means in our case that it references another column in another table. We can list those constraints with the following command.

1SELECT *
2FROM information_schema.referential_constraints
3WHERE constraint_schema = 'LARAVELDB';

The question in this case would give the following result.

1+--------------------+-------------------+-------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
2| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME         | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME |
3+--------------------+-------------------+-------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
4| def                | laraveldb         | books_author_id_foreign | def                       | laraveldb                | PRIMARY                | NONE         | RESTRICT    | RESTRICT    | books      | authors               |
5+--------------------+-------------------+-------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
61 row in set (0.00 sec)

The last two columns are the important ones for now, so we look at them separately.

1mysql> select table_name, referenced_table_name from information_schema.referential_constraints where constraint_schema = 'LARAVELDB';
2+------------+-----------------------+
3| TABLE_NAME | REFERENCED_TABLE_NAME |
4+------------+-----------------------+
5| books      | authors               |
6+------------+-----------------------+
71 row in set (0.00 sec)

We see the table name that has the constraint and which table it refers to.

I have prepared two records one in each of the table like so.

1mysql> select * from authors;
2+----+---------------+---------------------+---------------------+
3| id | name          | created_at          | updated_at          |
4+----+---------------+---------------------+---------------------+
5|  1 | Robert Jordan | 2020-12-24 18:16:35 | 2020-12-24 18:16:35 |
6+----+---------------+---------------------+---------------------+
71 row in set (0.00 sec)

1mysql> select * from books;
2+----+----------------------+-----------+---------------------+---------------------+
3| id | title                | author_id | created_at          | updated_at          |
4+----+----------------------+-----------+---------------------+---------------------+
5|  1 | The Eye Of The World |         1 | 2020-12-24 18:16:15 | 2020-12-24 18:16:15 |
6+----+----------------------+-----------+---------------------+---------------------+
71 row in set (0.00 sec)

Now if I want to delete the author I will run into the troublesome child.

1ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
2(`laraveldb`.`books`, CONSTRAINT `books_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))

One way to solve this is to delete the child record manually first then delete the parent record. However, there is a better way to handle this.

If we run this query

1select table_name, referenced_table_name, delete_rule from information_schema.referential_constraints
2where constraint_schema = 'LARAVELDB';

We will get another column that will provide us with some clarity.

1+------------+-----------------------+-------------+
2| table_name | referenced_table_name | delete_rule |
3+------------+-----------------------+-------------+
4| books      | authors               | RESTRICT    |
5+------------+-----------------------+-------------+
61 row in set (0.00 sec)

We have one delete rule that says restrict that is preventing us from deleting the author. We can add a cascading effect in our migration.

If we change our migration for the books' table like so

1Schema::create('books', function (Blueprint $table) {
2    $table->id();
3    $table->string('title');
4    $table->unsignedBigInteger('author_id');
5    $table->timestamps();
6    $table->foreign('author_id')->references('id')->on('authors')->onDelete('cascade');
7});

And we run the

1SELECT table_name, referenced_table_name, delete_rule
2FROM information_schema.referential_constraints
3WHERE constraint_schema = 'LARAVELDB';

Again we will see that our delete rule has changed.

1+------------+-----------------------+-------------+
2| table_name | referenced_table_name | delete_rule |
3+------------+-----------------------+-------------+
4| books      | authors               | CASCADE     |
5+------------+-----------------------+-------------+
61 row in set (0.01 sec)

So now if we try to delete the author the book will be deleted as well.

1mysql> select * from authors;
2+----+---------------+---------------------+---------------------+
3| id | name          | created_at          | updated_at          |
4+----+---------------+---------------------+---------------------+
5|  1 | Robert Jordan | 2020-12-24 18:16:35 | 2020-12-24 18:16:35 |
6+----+---------------+---------------------+---------------------+
71 row in set (0.00 sec)

1mysql> select * from books;
2+----+----------------------+-----------+---------------------+---------------------+
3| id | title                | author_id | created_at          | updated_at          |
4+----+----------------------+-----------+---------------------+---------------------+
5|  1 | The Eye Of The World |         1 | 2020-12-24 18:16:15 | 2020-12-24 18:16:15 |
6+----+----------------------+-----------+---------------------+---------------------+
71 row in set (0.00 sec)

Running this

1delete from authors where id = 1;

Will give us this response.

1mysql> delete from authors where id = 1;
2Query OK, 1 row affected (0.00 sec)

And both our tables are empty

1mysql> select * from authors;
2Empty set (0.00 sec)

1mysql> select * from books;
2Empty set (0.00 sec)

The refusal to add or update AKA ERROR 1452 (23000): Cannot add or update a child row:

This error is a distant cousin to the previous error. In this case the foreign key is preventing us from inserting a record in the books' table with an author_id that does not exist in the authors table.

1ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
2`laraveldb`.`books`, CONSTRAINT `books_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE)

This error might not be that common in Laravel, but I think it's good to know what causes it and how to solve it.

Take this piece of php code in the store method of our Book Controller.

1$book = new Book();
2$book->title = 'The Wizards First Rule';
3$book->author_id = 2;
4$book->save();

So the error above tells us that a foreign key constraint fails. That tells us that there is something with our constraint. If we take a look att our authors table, and it's contents we will see this.

1mysql> select * from authors;
2+----+---------------+------------+------------+
3| id | name          | created_at | updated_at |
4+----+---------------+------------+------------+
5|  1 | Robert Jordan | NULL       | NULL       |
6+----+---------------+------------+------------+
71 row in set (0.00 sec)

Now since we are using a foreign key constraint it means that our foreign key must exist as a primary key in our authors table. To solve this issue we need either to change the author_id in our insert or create a new author.

So if we create the author first

1$author = Author::create(['Terry Goodkind']);

Then we can create the book.

1$book = new Book();
2$book->title = 'The Wizards First Rule';
3$book->author_id = $author->id;
4$book->save();

This error is best avoided by using the exists validation rule before trying to insert a new record.

1'author_id' => 'required|exists:authors,id'

The irksome null value AKA SQLSTATE[23000]: Integrity constraint violation:

The full error message might look like this.

1SQLSTATE[23000]: Integrity constraint violation:
21048 Column 'title' cannot be null
3(SQL: insert into `books` (`title`, `author_id`, `updated_at`, `created_at`)
4values (?, 2, 2020-12-24 21:05:43, 2020-12-24 21:05:43))

This error simply means that you are trying to insert a null value into the given column. You can see that quite clearly if you look at the SQL code in the error message.

1(SQL: insert into `books` (`title`, `author_id`, `updated_at`, `created_at`)
2values (?, 2, 2020-12-24 21:05:43, 2020-12-24 21:05:43))

The title in this case should not be null and in the error message a null value is represented by a question mark. Sometimes it might be OK to have a null value then you should add ->nullable() to your migration.

As you can see demonstrated here

1$table->string('series')->nullable();

As a rule of thumb foreign key should almost never be nullable since it defeats the purpose of a foreign key.

So the easiest way to not get this kind of error is to validate the data before you try to insert it.

1'title' => 'required'

The missing default value AKA SQLSTATE[HY000]: General error: 1364 Field doesn't have a default value

This issue has to do with null being passed to a date field that isn't nullable.

1SQLSTATE[HY000]: General error: 1364 Field 'published_at' doesn't have a default value
2(SQL: insert into `books` (`title`, `author_id`, `pages`, `updated_at`, `created_at`)
3values (The eye, 2, 10, 2020-12-25 13:41:56, 2020-12-25 13:41:56))

There are a few ways to get around this issue.

• Make the field nullable

1$table->date('published_at')->nullable();

• Add a default value to the migration

1$table->date('published_at')->default(Carbon::now());

And the mandatory validation of the field.

1'published_at' => 'required'

The truncated value AKA SQLSTATE[22001]: String data, right truncated:

To illustrate this issue we make a small change in our books' migration.

1Schema::create('books', function (Blueprint $table) {
2    $table->id();
3    $table->string('title', 10);
4    $table->string('series')->nullable();
5    $table->unsignedBigInteger('author_id');
6    $table->timestamps();
7    $table->foreign('author_id')->references('id')->on('authors')->onDelete('cascade');
8});

We but a size limit of ten characters on the title field. If we then try to insert a title longer than ten characters into our table the database will throw this kind of error message.

1SQLSTATE[22001]: String data, right truncated: 1406
2 Data too long for column 'title' at row 1
3 (SQL: insert into `books` (`title`, `author_id`, `updated_at`, `created_at`)
4 values (The eye of the world, 2, 2020-12-25 13:19:24, 2020-12-25 13:19:24))

This one is pretty straight forward and tells us the value is too long for title. This error is easily avoidable by validating the length of the value passed.

1'title' => 'max:10'

If you want to allow the user to insert longer values increase the length of the field.

The value issue AKA SQLSTATE[HY000]: General error: 1366 Incorrect value:

This error occurs when you try to insert a value of incorrect type. Let's say that you have a numeric field in your database and the user tries to submit the string ten instead ot the numeric value 10.

1SQLSTATE[HY000]: General error: 1366
2Incorrect integer value: 'ten' for column 'pages' at row 1
3(SQL: insert into `books` (`title`, `author_id`, `pages`, `updated_at`, `created_at`)
4values (The eye, 2, ten, 2020-12-25 13:31:39, 2020-12-25 13:31:39))

This can once again be avoided with the correct validation rules. In this case a simple check that the value is numeric will suffice.

1'pages' => 'numeric'

Good luck with your application, Tray2