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.
Tag: how to
3 JSON Utility Functions in MySQL
MySQL provides us with a few utility functions that act on JSON values, or strings that can be parsed as JSON values.
Below are three JSON utility functions that we can use for such tasks.
Continue readingHow to Add Headings for WITH ROLLUP in MySQL
MySQL provides us with the WITH ROLLUP modifier for the GROUP BY function for generating super aggregate data.
But by default, this modifier doesn’t label its data. By this I mean, it doesn’t provide us with meaningful labels for the rows that it generates. It simply uses NULL in those rows.
Maybe you’ve encountered this and are now looking for a solution.
Below is a handy little technique we can use to replace NULL with our own headings for these rows.
Fix “WRONGTYPE Operation against a key holding the wrong kind of value” when using ZINCRBY in Redis
If you get an error that reads “WRONGTYPE Operation against a key holding the wrong kind of value” when using the ZINTER command in Redis, it’s probably because you’re passing a key with the wrong data type.
To fix this issue, be sure that the key you pass to the ZINTER command holds a sorted set.
How to Create a CHECK Constraint Against a JSON Column in MySQL
When we create or modify a table in MySQL, we have the option of applying a CHECK constraint against one or more columns. This allows us to check the data before it enters the database. Data can only enter the database if it doesn’t violate the rules in our CHECK constraint.
If the column is a JSON column, we have the option of creating a CHECK constraint that checks that the JSON document doesn’t violate its JSON schema. To do this, we can include the schema in the CHECK constraint.
Fix “column … can only be updated to DEFAULT” in PostgreSQL
If you’re getting an error in PostgreSQL that tells you that a column “…can only be updated to DEFAULT” with detail that explains that it “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to update an identity column with your own value, but the identity column was created with the GENERATED ALWAYS option.
Create a CHECK Constraint in MySQL
As of MySQL 8.0.16, we can create CHECK constraints in MySQL.
A CHECK constraint is a type of integrity constraint. It specifies a search condition to check the value being entered into a row. If the value being entered violates the CHECK constraint, then the result of the search condition is FALSE and an error occurs (unless the IGNORE clause is used in the SQL statement, in which case a warning is reported and the offending row is skipped).
The ability to create CHECK constraints was introduced in MySQL 8.0.16. Prior to version 8.0.16, MySQL actually allowed a limited version of CHECK constraint syntax, but it was completely ignored (no CHECK constraint was created or evaluated).
6 Ways to Fix “nextval: reached minimum value of sequence” in PostgreSQL
If you’re getting an error that reads something like “nextval: reached minimum value of sequence “sequence1” (-3)” in PostgreSQL, it’s probably because you’re trying to generate a new value from a descending sequence, but the sequence has already reached its minimum value.
We have a number options when it comes to dealing with this issue.
Continue readingHow to Select Rows Where a Column is NULL in SQL
When learning SQL, a common mistake when checking for NULL values is to use an equality operator. In SQL, a NULL value is treated a bit differently to other values.
In SQL we use IS NULL instead of = NULL. Likewise, we use IS NOT NULL instead of <> NULL or != NULL to select those columns that don’t have a NULL value.
3 Ways to Get the Data Directory in MySQL
In MySQL, the data directory stores information managed by the MySQL server. Each subdirectory of the data directory is a database directory and corresponds to a database managed by the server.
If you ever need to find out where the data directory is located on your MySQL implementation, below are some options to try.
Continue reading