[OpenSER-Devel] Defining MySQL foreign key constraint

[Mircea Amarascu]

Hello Henning,

1. Yes, this worked, however I saw a problem (maybe I'm doing something 
wrong):

I have the following XML description of my table:

<table id="my_table" xmlns:db="http://docbook.org/ns/docbook">
    <name>my_table</name>
    <version>2</version>
    <type db="mysql">&MYSQL_TABLE_TYPE;</type>

   ....

    <index>
        <name>diversion_subscriber_id_exists</name>
        <colref linkend="subscriber_id"/>
    </index>
   
    <column>
        <null/>
        <type db="mysql">CONSTRAINT `my_table_subscriber_id_exists` 
FOREIGN KEY
          (`subscriber_id`) REFERENCES `subscriber` (`id`) ON DELETE CASCADE
        </type>
    </column>

</table>

The sql script for MySQL is generated correctly, however I receive this 
error for postgres and dbtext:

ERROR: Table: my_table, column:  - unsupported column type: CONSTRAINT 
`my_table_subscriber_id_exists` FOREIGN KEY (`subscriber_id`) REFERENCES 
`subscriber` (`id`) ON DELETE CASCADE.

My impression was that the column should have been ignored by postgres 
and dbtext, because of the db="mysql" attribute.

2. This second issue is not related to the first one:

The MySQL tables are created with Type=MyISAM|InnoDB, instead of 
ENGINE=MyISAM|InnoDB, the result being a warning from MySQL:

| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 
'ENGINE=storage_engine' instead

MySQL documentation says:

The ENGINE and TYPE options specify the storage engine for the table. 
ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is 
the preferred option name as of those versions, and TYPE has become 
deprecated. TYPE is supported throughout the 4.x series, but likely will 
be removed in the future.

so maybe we could also change TYPE to ENGINE, if everybody is fine with 
it (there aren't people who use older versions of MySQL).


Thanks.



Henning Westerholt wrote:
> 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
>