[MDEV-12094] Foreign key meta-data for MyISAM Created: 2017-02-20  Updated: 2023-10-07

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Olaf van der Spek Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-16417 Store Foreign Key metadata outside of... In Review

 Description   

Foreign keys aren't supported by MyISAM (AFAIK) and the data is discarded after create table.
It'd be nice if the relations data wasn't discarded such that GUI tools like phpMyAdmin can use the information.

 Comments   
Comment by Sergei Golubchik [ 2017-05-25 ]

Won't it be confusing to show foreign keys that aren't enforced?

I suppose SHOW CREATE TABLE can show them just as it shows unsupported table attributes — inside a comment. But then GUI tools won't be able to see them anyway.

Comment by Olaf van der Spek [ 2017-05-26 ]

Maybe
The goal is for phpMyAdmin to know the key references another table.

Perhaps per-field meta-data would be a better way to store this.

Comment by Sergei Golubchik [ 2017-05-26 ]

I don't see any possible fix that could be done just on MariaDB side, without any changes to phpMyAdmin.

And if phpMyAdmin has to be modified, then we do the fix with showing foreign keys in a comment. It'll be consistent with:

MariaDB [test]> set sql_mode=IGNORE_BAD_TABLE_OPTIONS;
 
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> create table t1 (a int foobar=blabla) century=21;
 
Query OK, 0 rows affected, 2 warnings (0.00 sec)
 
MariaDB [test]> show create table t1\G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `a` int(11) DEFAULT NULL `foobar`=blabla
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 `century`=21
 
1 row in set (0.00 sec)
 
MariaDB [test]> set sql_mode='';
 
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> show create table t1\G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `a` int(11) DEFAULT NULL /* `foobar`=blabla */
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /* `century`=21 */
 
1 row in set (0.00 sec)

Note, how unsupported attributes are printed inside a comment when IGNORE_BAD_TABLE_OPTIONS is not set.

Generated at Thu Feb 08 07:55:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.