SQL Pitfalls

...

1. Length of VARCHAR index is limited to 767 bytes. It equals 255 characters when assuming a UTF-8 character set and the maximum of 3 bytes for each character.

   Code Block
   language ruby
   add_index :my_table, :my_column, :name => 'index_name', :length => 255

2. This is possible to link some tables when deleting some rows :

   Code Block
   sql
   DELETE mt FROM my_table mt, another_table at WHERE at.id = mt.id

3. Hibernate native query

   Code Block
   createNativeQuery("delete from duplications_index e where e.snapshot_id in (:ids)")

   fails with message 

   No Format
   You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'e where e.snapshot_id in (...)' at line 1 javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query

   and should be replaced by 

   Code Block
   createNativeQuery("delete from duplications_index where snapshot_id in (:ids)")

4. Timestamp precision is limited to the second. Starting from MySQL 5.6.4 it is possible to explicitly create a TIMESTAMP column with a finer precision.

MSSQL

1. MyBatis : instead of constants "TRUE" and "FALSE" use "1" and "0" respectively.
2. If there is an index on column, which should be resized or modified, then it should be dropped and then re-created after changes for column. Otherwise you will get "The index '...' is dependent on column '...'."
3. Table names are case-sensitive. In Sonar they are lower-case. That's why MyBatis mappers must be lower-case. Otherwise the error is something like "Did not find table 'RULES'".

4. Delete with joins. Note that table alias seems to be not supported :

   Code Block
   language sql
   DELETE my_table FROM my_table INNER JOIN another_table ON my_table.is=another_table.is WHERE another_table.xxx=...