[OpenSER-Devel] Defining MySQL foreign key constraint

[Mircea Amarascu]

Hello,

Thanks for the answers.

I've discovered one more thing while creating my tables, that we could 
also need TIMESTAMP column types besides DATETIME (in MySQL at least, I 
don't know the correspondence for the other database engines.) One 
advantage of TIMESTAMP 
(http://dev.mysql.com/doc/refman/5.0/en/timestamp.html) is for example 
that you can specify |both DEFAULT CURRENT_TIMESTAMP| and |ON UPDATE 
CURRENT_TIMESTAMP| clauses, and so the column will automatically updated 
to the current timestamp (this is useful in 'last_modified' columns).

Henning Westerholt wrote:
> On Thursday 12 July 2007, you wrote:
>   
>> 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">
>> [..]
>> </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.
>>     
>
> Hello Mircea,
>
> this error is actually a feature, to prevent you from specifying only a type 
> for one database, and forget about the others.. ;-)
>
> But for your purposes it should work if you add two "type" defines for 
> postgresql and dbtext, or uncomment the databases you don't need in the 
> Makefile.
>
> E.g.:
>
> <type db="mysql">......</type>
> <type db="postgres"></type>
> <type db="dbtext">invalid</type>
> <null/>
>
> This way you will get an empty column in postgresql, and a actual but not used 
> column for dbtext. 
>
>   
>> 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).
>>     
>
> This is fine for me. Lets wait a little bit if somebody object against the new 
> syntax, then i'll do this change next week.
>
> Cheers,
>
> Henning
>