DBMS – Page 102

5 Ways to Fix Error 1364 “Field ‘…’ doesn’t have a default value” in MySQL

July 16, 2023 by Ian

If you’re getting MySQL error 1364, that reads something like Field 'column_name' doesn't have a default value, where column_name is the name of a column in your table, it’s probably because you’re trying to perform an operation that retrieves the column’s default value, but it doesn’t have a default value.

This can happen when the column is set to NOT NULL and hasn’t been defined with an explicit default value.

The solution to this issue will depend on what we’re trying to do. Below are examples of how to fix this error.

Understanding the sys.quote_identifier() Function in MySQL

July 16, 2023July 15, 2023 by Ian

In MySQL, we can use the sys.quote_identifier() function to quote a string to produce a result that can be used as an identifier in a SQL statement.

The string is returned enclosed by backticks (`), with each instance of a backtick doubled.

This function can be handy when a value to be used as an identifier is a reserved word or contains backtick characters.

How to List All INFORMATION_SCHEMA Views in MySQL

July 15, 2023July 14, 2023 by Ian

The information schema is a standard set of read-only views that provide metadata about objects in a database. These views are typically accessed by qualifying the view name with INFORMATION_SCHEMA (for example INFORMATION_SCHEMA.TABLES). But before we do this, we need to know which views are available in the first place.

3 Ways to List Sequences in PostgreSQL

July 12, 2023 by Ian

We have a number of options when it comes to returning a list of sequences in PostgreSQL. By “sequences”, I mean “sequence objects”, or “sequence generators”. These are objects we can create in order to generate a series of sequential numbers.

Here are three ways to get a list of sequences in Postgres.

Introduction to the sys.ps_thread_stack() Function in MySQL

July 12, 2023July 11, 2023 by Ian

In MySQL, the sys.ps_thread_stack() function returns a JSON formatted stack of all statements, stages, and events within the Performance Schema for a given thread ID.

2 Ways to Add a DEFAULT Clause to an Existing Column in MySQL

July 10, 2023 by Ian

If you have an existing column in MySQL that you want to add a DEFAULT clause to, the following example may help.

To explicitly add a DEFAULT clause to an existing column in MySQL, we can use the ALTER TABLE statement along with the MODIFY clause.

We can also add a DEFAULT clause implicitly by defining a column as nullable. When we do this, MySQL implicitly adds its own DEFAULT clause.

Understanding the sys.extract_schema_from_file_name() Function in MySQL

July 9, 2023 by Ian

In MySQL, the sys.extract_schema_from_file_name() function is a stored function in the sys schema.

It accepts a path name, and returns the path component that represents the schema name.

This enables us to get the schema from a given file path.

How to Enforce a CHECK Constraint in MySQL

August 9, 2024July 8, 2023 by Ian

If we have a CHECK constraint that’s currently not enforced, we can easily change this so that it’s enforced.

We can use the following code to enforce a CHECK constraint in MySQL.

Fix Error 1138 “Invalid use of NULL value” in MySQL

July 7, 2023 by Ian

If you’re getting error 1138 that reads “invalid use of NULL value“, it could be because you’re trying to change the definition of a column to require non-NULL values, but the column contains NULL values.

This can happen when we try to change a column from NOT NULL to NULL. It can also happen when we try to set a column as a primary key, but that column contains NULL values.

To fix this issue, be sure to ensure that the column you’re modifying doesn’t contain any NULL values. Alternatively, if the column should be able to accept NULL values, then you should change your column definition to allow NULL values.

How to Add a CHECK Constraint to an Existing Table in MySQL

August 9, 2024July 6, 2023 by Ian

As of MySQL 8.0.16, we can create CHECK constraints in MySQL. We can create them at the time of creating the table, or we can add the constraint later.

To add a CHECK constraint to an existing table, we can use the ALTER TABLE statement.