Bug #25031 No foreign key definition passed to storage engine

• Submitted: 13 Dec 2006 9:35
• Modified: 20 Jan 2007 14:29
• Reporter: Jan Lindström
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 5.0 & 5.1
• OS: Any (Any)
• CPU Architecture: Any

[13 Dec 2006 9:35] Jan Lindström

Description:
Issue following SQL-clauses:

create table test1 (a int auto_increment primary key) engine=soliddb;
create table test2 (a int auto_increment primary key, b int references
test1.a) engine=soliddb;

However, in storage engine you can see only the primary key not the foreign key:

Breakpoint 1, ha_soliddb::create (this=0x97f0b18, name=0x6c8c9158 "./test/test3.frm", table_arg=0x6c8c867c, create_info=0x97d3454) at ha_soliddb.cc:9943
(gdb) p table->s->keys
$1 = 1
(gdb) p thd->lex->key_list.elements
$2 = 1
(gdb) 

How to repeat:
create table test1 (a int auto_increment primary key) engine=soliddb;
create table test2 (a int auto_increment primary key, b int references
test1.a) engine=soliddb;

Suggested fix:
MySQL should fill thd->lex->sql_keys with key information and the same key information it passes to the storage engine and in the same order. Note that this problem does not exists on InnoDB because it parses SQL-query itself. Thus the problem is in the MySQL parser.

[20 Jan 2007 14:29] Sergei Golubchik

It's not obvious that foreign keys should be in table->s->keys, they are contraints, not indexes for the optimizer.

Anyway, there's a task in TODO - provide an API for storage engines to access foreign key definitions, check foreign key constraints, and so on. But it's still in the design phase, so it's a bit too early to implement a specific solution.