RE: Foreign Key constraints on Sybase?

[Michael Vorburger]

self.reply ;-) with what I learnt from our DB expert about this since writing 
for future Googlers:

Sybase indeed SUPPORTS foreign key constraints ("ALTER TABLE A_B ADD FOREIGN 
KEY (A_id) REFERENCES A (id);").  The SybaseDictionary MUST emit these (ignore 
cascades), IMHO.  I'd argue this is an OpenJPA bug?

Sybase does unfortunately NOT seem to support "...  ON DELETE CASCADE".  A 
(simple!) TSQL stored proc is typically written for this functionality.  
SybaseDictionary COULD probably generate such stored procs for ideal Sybase 
support.

Comments, anybody?

 

-----Original Message-----
From: Michael Vorburger [mailto:[EMAIL PROTECTED] 
Sent: mardi, 26. février 2008 19:57
To: users@openjpa.apache.org
Subject: Foreign Key constraints on Sybase?

Hello,
 
I am trying to get Foreign Key constraints generated by the mappingtool on 
Sybase.
 
After some doc reading and playing around I found mappingtool ...
-foreignKeys true, and tried e.g. 

<property name="openjpa.jdbc.MappingDefaults"
value="ForeignKeyDeleteAction=cascade,
JoinForeignKeyDeleteAction=restrict" /> <!-- Or just restrict? -->

and tried the @ElementForeignKey annotation too, but no mater what, I was 
constantly awarded with sad messages like this one only:
 
1877  Test-JPA WARN   [main] openjpa.jdbc.Schema - The foreign key
"<foreignkey>" was not added to table "A".

I played around with this some more (too long) until it occured to me to try 
switching to Derby just for a try - and I got the "ALTER TABLE A_B ADD FOREIGN 
KEY (A_id) REFERENCES A (id);" kind of DDL!
 
So this wasn't my missconfiguration as I had assumed for hours, but a [EMAIL 
PROTECTED] "problem"?? Then I dug into the SybaseDictionary and found this:
public String[] getAddForeignKeySQL(ForeignKey fk) {
    // Sybase has problems with adding foriegn keys via ALTER TABLE command
    return new String[0];
}

why are you doing this?? As far as I can tell, an ALTER TABLE like the one 
shown above seems to be pass just fine on Sybase... so what's the deal with 
Foreign Key constraints on Sybase in OpenJPA? There is nothing documented on 
http://openjpa.apache.org/docs/latest/manual/dbsupport_sybase.html#dbsup
port_sybase_issues about this.
 
Thank you very much,
Michael
 
PS: I did notice Sybase doesn't seem to like "...  ON DELETE CASCADE", I will 
ask somebody here who speaks Sybase SQL more fluently than I do about this. 
Surely there is an alternative syntax?
 
 
_____________________________
Michael Vorburger, Odyssey Financial Technologies Direct phone:  +41 21 310 00 
86 (OAMS VOIP: 1086) Cell phone: +41 78 805 5541
Mailto: [EMAIL PROTECTED]
 

____________________________________________________________

* This email and any files transmitted with it are CONFIDENTIAL and intended
  solely for the use of the individual or entity to which they are addressed.
* Any unauthorized copying, disclosure, or distribution of the material within
  this email is strictly forbidden.
* Any views or opinions presented within this e-mail are solely those of the
  author and do not necessarily represent those of Odyssey Financial 
Technologies SA unless otherwise specifically stated.
* An electronic message is not binding on its sender. Any message referring to
  a binding engagement must be confirmed in writing and duly signed.
* If you have received this email in error, please notify the sender immediately
  and delete the original.

____________________________________________________________

• This email and any files transmitted with it are CONFIDENTIAL and intended
  solely for the use of the individual or entity to which they are addressed.
• Any unauthorized copying, disclosure, or distribution of the material within
  this email is strictly forbidden.
• Any views or opinions presented within this e-mail are solely those of the
  author and do not necessarily represent those of Odyssey Financial
Technologies SA unless otherwise specifically stated.
• An electronic message is not binding on its sender. Any message referring to
  a binding engagement must be confirmed in writing and duly signed.
• If you have received this email in error, please notify the sender immediately
  and delete the original.