Hibernate generates tables but is not creating foreign constraints when deployed on a Linux box (CentOS) with mysql running on Jboss. The surprising thing is that when the same application is deployed on windows (the platform on which it was developed) , with mysql on Jboss everything works perfectly. This has caused so much issue as the application had been tested on windows and has passed but has to be deployed to a linux box on which it generates all sorts of errors. We discovered that the foreign keys are not generated when deployed on linux but were generated when the application is deployed on windows so this might be the culprit responsible.
Please help is urgently needed.

May this works:

PLz check the mysql storage engine...MyISAM or Innodb....(in linux and also in windows)
command--show variables (at mysql command prompt)
see the variable storage_engine=" "

if MyISAM
MyISAM tables do not support transactions or foreign key constraints.

while innodb does..

Reply soon..if works


Regards,
Parag Gajbhiye

thanks parag.gajbhiye, I discovered that the default storage engine is myissam while it is Innodb on windows. please how can I change it to Innodb on the linux box. I followed instructions that I saw in the mysql documentation opened the my.cnf, I didn't see the variable (default-storage-engine) so I added the following:
default-storage-engine=INNODB
then I restarted mysql but the default was still MyIssam. Please how can I change this to Innodb (as default). Thanks

Problem solved. I restarted the database again and redeployed the application on the linux box. I now have my foreign key constraints. I changed the default engine of mysql in my.cnf. the content of my.cnf is as below:

[mysqld]
default-storage-engine=InnoDB
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql


I restarted mysql server with the following command

/sbin/service mysqld restarted

when I issued the command show engines I now got

+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

This clearly shows that InnoDB is now the default table type.
I hope this helps someone. Thanks