[OpenSER-Devel] Defining MySQL foreign key constraint

[Henning Westerholt]

On Wednesday 11 July 2007, Mircea Amarascu wrote:
> Hello,
>
> I've written an OpenSER module that uses tables with the following
> structure:
>
> CREATE TABLE `my_table` (
> [..]
>   CONSTRAINT `my_table_subscriber_id_exists` FOREIGN KEY
> (`subscriber_id`) REFERENCES `subscriber` (`id`) ON DELETE CASCADE
> ) ENGINE=InnoDB
>
> they link to the subscriber table using a foreign key constraint.
>
> I'd like to ask how can I express this structure in DB schema's XML
> format, because I haven't found any example of something similar. Can
> you give me an answer, or point me to the appropriate documentation or
> references ?

Hello Mircea,

foreign keys constraints are unfortunaly not supported at the moment. Most 
people using MyISAM because of performance reasons, and there are also not 
available in dbtext. In oder to support this you could extend the XSL scripts 
to generate this statements. 

You could use the following statement in your xml definition to create the 
table with CONSTRAINTs:

column>
  <type db="mysql">CONSTRAINT `my_table_subscriber_id_exists` FOREIGN KEY    
  (`subscriber_id`) REFERENCES `subscriber` (`id`) ON DELETE CASCADE</type>
</column>

This uses the 'standard' column definition and will simply copy the given SQL 
code into the generated table.

Does this work for you?

Henning