Bug #34652 INFORMATION_SCHEMA: ENGINES should tell whether FKs are supported
Submitted: 19 Feb 2008 0:22 Modified: 20 Feb 2008 19:13
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.1 and higher OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: qc

[19 Feb 2008 0:22] Peter Laursen
Description:
feature request:

I request a column added to I_S.engines table named 'ForeingKeys' or similar.

This column should tell if Foreign Keys are available for the engine and (if I understand correctly that 6.1 will introduce a syntax based on Standard SQL and not InnoDB) what syntax is used with that particular engine and version.

Values for such columns could be yes/no or yes/innodb/standard.

Such I_S extension would make it possible for client programs (such as GUI clients) to give user an option to create FK's when it is possible and only then (without hardcoding information that will sooner or later be outdated), and generate the correct syntax for it!

This is equally relevant to the 'transactions' and 'xa' columns currently there

How to repeat:
show create table information_schema.engines
-- version is 6.0.4-alpha-pb34

CREATE TEMPORARY TABLE `ENGINES` (                
           `ENGINE` varchar(64) NOT NULL DEFAULT '',       
           `SUPPORT` varchar(8) NOT NULL DEFAULT '',       
           `COMMENT` varchar(80) NOT NULL DEFAULT '',      
           `TRANSACTIONS` varchar(3) NOT NULL DEFAULT '',  
           `XA` varchar(3) NOT NULL DEFAULT '',            
           `SAVEPOINTS` varchar(3) NOT NULL DEFAULT ''     
         ) ENGINE=MEMORY DEFAULT CHARSET=utf8 

Suggested fix:
yup :-)
[19 Feb 2008 0:23] Peter Laursen
ouch ... :-(

Values for such column could be yes/no or NO/innodb/standard.
[19 Feb 2008 0:33] Peter Laursen
and title of this post should read

"Information_Schema should tell for ENGINES if FK's are supported"
[19 Feb 2008 0:46] Paul DuBois
Updating synopsis
[19 Feb 2008 1:05] MySQL Verification Team
Thank you for the bug report.
[19 Feb 2008 13:29] Sergei Golubchik
Uh, I don't know if it's a good idea.
We plan to have foreign key support for all engines implemented in the server, not in the storage engine (but using storage engine implementation as an optimization, if possible). It means that in the future "if FK's are supported" won't be a storage engine property, and if such a column would be added, it'll need to be removed. As you understand, removing a column from I_S table is a lot more difficult than adding (for backward compatibility reasons).

I'd rather suggest you to have a special check for InnoDB now (if engine_name == "InnoDB"), unlike a new column it won't cause any compatibility problems in the future.
[19 Feb 2008 13:51] Peter Laursen
ok .. :-)

I did not know details of implemetation plans.
But currently we will also need to check for 3rd party engines with FK support (Solid, Primebase) as our program (SQLyog) is not 'locked' to engines from MySQL.  But with a pluggable storage engine architechture new engines may become available without we are notified about it!  Asking the server would be best option I think!

In 6.1 it will then always be 'yes' if implementation will be successfull for all engines - what I might doubt!  Also still third party engines and 'small and simple' engines may not want to use that server FK layer!

BTW: do future plan include abandoning InnoDB with MySQL or will the ENGINE FK support (when avail) be removed or 'bypassed' and the server functionality applied instead ?
[19 Feb 2008 14:28] Sergei Golubchik
Answer to BTW: As far as I know, there're no plans to abandon InnoDB :)
And we cannot possibly remove engine's FK support from InnoDB or SolidDB or PBXT.
We can, of course, not to call it, bypass as you say. But I believe the ideal approach would be to use it when it makes sense, as it'll probably be faster. And only do the checks in the server if storage engine cannot do them (e.g. FK relationships between tables in different storage engines, or cascade delete action and on delete trigger).
[20 Feb 2008 19:13] Peter Laursen
from Sergei's reply I would think that values like no|engine|server would be appropriate for that column!