Forum OpenACS Q&A: Response to mysql passes the acid test?

Definitely doesn't support foreign keys and subselects. A client is requesting that I use it for a project and the lack of sub-selects has wasted a lot of my time. From the documentation:

The following will not yet work in MySQL: 

SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);

However, in many cases you can rewrite the query without a 
sub-select: 

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL

A whole new syntax for me. Access is the same but, in its favour, if you directly type in sql using subselects, it will translate it into the 'left join' syntax. I've found this a useful little tool to figure out the other syntax. Just seems to me that sub-selects are so much easier to wrap my head around. As for foreign keys, here's what the docs say:

Note that foreign keys in SQL are not used to join tables, but are
used mostly for checking referential integrity (foreign key 
constraints). If you want to get results from multiple tables from a 
SELECT statement, you do this by joining tables: 

SELECT * from table1,table2 where table1.id = table2.id;

See section 7.20 JOIN Syntax. See section 9.3.6 Using Foreign Keys. 

The FOREIGN KEY syntax in MySQL exists only for compatibility with 
other SQL vendors' CREATE TABLE commands; it doesn't do anything. The 
FOREIGN KEY syntax without ON DELETE ... is mostly used for 
documentation purposes. Some ODBC applications may use this to 
produce automatic WHERE clauses, but this is usually easy to 
override. FOREIGN KEY is sometimes used as a constraint check, but 
this check is unnecessary in practice if rows are inserted into the 
tables in the right order. MySQL only supports these clauses because 
some applications require them to exist (regardless of whether or not 
they work). 

And then they go on to explain why foreign keys shouldn't be used...

There are so many problems with foreign key constraints that we don't 
know where to start: 

Foreign key constraints make life very complicated, because the 
foreign key definitions must be stored in a database and implementing 
them would destroy the whole ``nice approach'' of using files that 
can be moved, copied, and removed. 

The speed impact is terrible for INSERT and UPDATE statements, and in 
this case almost all FOREIGN KEY constraint checks are useless 
because you usually insert records in the right tables in the right 
order, anyway. 

There is also a need to hold locks on many more tables when updating 
one table, because the side effects can cascade through the entire 
database. It's MUCH faster to delete records from one table first and 
subsequently delete them from the other tables. 

You can no longer restore a table by doing a full delete from the 
table and then restoring all records (from a new source or from a 
backup). 

If you use foreign key constraints you can't dump and restore tables 
unless you do so in a very specific order. 

It's very easy to do ``allowed'' circular definitions that make the 
tables impossible to re-create each table with a single create 
statement, even if the definition works and is usable. 

It's very easy to overlook FOREIGN KEY ... ON DELETE rules when one 
codes an application. It's not unusual that one loses a lot of 
important information just because a wrong or misused ON DELETE rule. 

The only nice aspect of FOREIGN KEY is that it gives ODBC and some 
other client programs the ability to see how a table is connected and 
to use this to show connection diagrams and to help in building 
applicatons. 

And I was under the impression that referential integrity was kind of important... Just my two cents worth.