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.
Ian
Understanding Window Functions in SQL
Window functions can be a useful tool when writing SQL queries. They allow us to include aggregate data across multiple rows without getting those pesky errors that sometimes occur when we try to use an aggregate function in the wrong way.
In this article, I aim to provide a simple overview of window functions and how they can be used to provide a more useful result set when running SQL queries.
3 Ways to List Sequences in PostgreSQL
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
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
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
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
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
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
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.
SERIAL vs AUTO_INCREMENT in MySQL
If you’ve ever encountered a SERIAL column in a MySQL database, you might’ve wondered how it differs from an AUTO_INCREMENT column. After all, they both generate values that automatically increment?
Read on to find out what the difference is between SERIAL and AUTO_INCREMENT in MySQL.