Introduction In this article, we are going to test the MySQL 8 implementation of custom SQL CHECK constraints. Although the CHECK clause is a standard SQL feature, prior to MySQL 8.0.16, the clause was parsed and ignored, so this functionality could only be emulated via BEFORE INSERT and UPDATE triggers. Custom SQL CHECK constraints As I explained in this article, custom SQL CHECK constraints are very useful to ensure non-nullability constraints for JPA entity subclass-specific attributes when using the SINGLE TABLE JPA inheritance strategy. To understand the problem, consider we have the... Read More
MySQL (really) supports CHECK CONSTRAINT since version 8.0.16. In this article I will show you 2 things: - An elegant way to simulate check constraint in MySQL 5.7 & 8.0. - How easy & convenient it is to use CHECK constraints in 8.0.16.
About any product, be it computer hardware, software or any other product, has features that are annoying to some of us. But few products has so many features that are annoying to just about everyone as computer software. And among computer software, database software in particular seems to to have these features, which some people seems to like, and some just find annoying. And then there are features, or lack of them or implementation specific details that seems to annoy just about everyone. Things that work in a partuicular way because someone, somewhere, in some distant universe, had the notion that this was a good thing. Often features relating back to ancient times. And sometimes features that you just know work in a weirdo way because the person, if it was a person, figuring out the feature of the implementation of it, really must have been smoking something that is illegal in many parts of the world. Which is not to say that these features …[Read more]
After I moved back to Europe and Malta in order to set up
operations here, I was approached by a old friend of mine who
wanted to know how to add a
UNIQUE constraint and
remove duplicates on a table, while keeping the newest records.
He had been trying with
ALTER TABLE but ran into
problems as the older values were taken.
So, to help him out, I first solved it based on his original idea, and then figured I would post a small note about the solution here.
So, let’s say we have the following structure . . .
sql01 blogs> SHOW CREATE TABLE post1164\G *************************** 1. row *************************** Table: post1164 Create Table: CREATE TABLE `post1164` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(5) DEFAULT NULL, `c` varchar(5) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM AUTO_INCREMENT=7 …[Read more]