I have been trying to do some test cases and see that when a normal FK is created, we get a _0 table name as well. I have reviewed other discussions on the forum related to this topic and came to know that this table is created when the chck is performed by database and is called a Hard RI.
While using 'with no chck option' (soft RI) doesn't create the _0 table, I was curious to know why does using 'with check option' doesnt create _0 table. As evident from my test cases below, with check option does return error but the error code is different.
error 2700 in case of hard RI and 3513 in case of with check option
Is using with check option same as enforcing a hard RI?
Any help in making me understand this behaviour would be great.
There's no way to get the different types of RI from the system tables, only SHOW will return it.
Regarding the original post:
WITH CHECK OPTION checks using EXISTS/NOT EXISTS on a statement level, you can see this in Explain