From: Dmitry Lenev
Date: May 11 2012 7:38am
Subject: Re: pointers on implementing foreign keys for a storage engine
List-Archive: http://lists.mysql.com/internals/38515
Message-Id: <20120511073828.GA2909@jubjub>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hello Zardosht!

* Zardosht Kasheff <zardosht@stripped> [12/05/08 19:33]:
> Hello all,
> 
> Are there any pointers for how one would go about implementing foreign
> keys in the storage engine? I cannot seem to figure it out from the
> handler API.

I am afraid there is no API which covers full lifecycle of a foreign key
at the moment.

What we have now is part of API which is responsible for providing
information about foreign keys in SE to SQL-layer, to be used by I_S
implementation, SHOW CREATE TABLE statement and some, but not all,
DDL statements like TRUNCATE TABLE, and some DML statements.

This part of API consists of the following handler methods/declarations:

 /* Gets foreign key create string from InnoDB for SHOW CREATE TABLE. */
 char* get_foreign_key_create_info();
 void free_foreign_key_create_info(char* str);

 struct st_foreign_key_info;
 typedef struct st_foreign_key_info FOREIGN_KEY_INFO;
 
 /*
   Used by I_S implementation to get info about foreign keys for I_S
   tables.
 */
 int get_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list);

 /*
   Used by TRUNCATE TABLE to figure out if table is referenced by some
   other table.
 */
 int get_parent_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list);


/*
  Used by TRUNCATE, REPLACE and RBR code to figure out if table is
  referenced.
*/
uint referenced_by_foreign_key() { return 0;}

/* Used by partitioning code. */
bool is_fk_defined_on_table_or_index(uint index);

/*
  Used by error-reporting code to get information about foreign key
  which caused duplicate key problem during update of parent table.
*/
bool get_foreign_dup_key(char *child_table_name, uint child_table_name_len,
                         char *child_key_name, uint child_key_name_len);


As I have said above there is no real API for creating and dropping
foreign keys during DDL.

InnoDB does this by parsing text of original statement from within certain
storage engine calls (e.g. rename_table()), which are invoked during CREATE
and ALTER TABLE statements. I am not sure that it is a good way to choose
if you are implementing FK support in engine from the scratch. Maybe it is
better to rely on structures that SQL-layer parser produces for foreign keys.
These structures are constructed by parser anyway but just not passed
explicitly to SE.

Hope this helps!

Let me know if you have additional questions!

-- 
Dmitry Lenev, Software Developer
Oracle Development SPB/MySQL, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification