The MySQL default storage engine MyISAM does not support transactions. In 

this table type the developer of MySQL followed another paradigm for data 

integrity called  atomic operations , as shown in Example 1 2. Changes 

produced by individual statements are committed to the database 

immediately as soon as they execute. In other words, a 

COMMIT

 statement 

implicitly follows each statement. To ensure database integrity code has to be 

provided in the application if required. 

MySQL supports transactions with the InnoDB and BDB transactional storage 

engines. InnoDB provides full ACID compliance.

Example 1 2   Atomic operation

INSERT INTO table1 ... 

COMMIT

DELETE FROM table2 ... 

COMMIT

Referential integrity

Referential integrity ensures that relationships between tables remain 

consistent. When one table has a foreign key to another table, the concept of 

referential integrity states that it is not allowed to add a record to the table that 

contains the foreign key unless there is a corresponding record in the linked 

table. In MySQL Server, MyISAM tables do not support foreign key 

constraints. MySQL only parses the FOREIGN KEY syntax in 

CREATE TABLE

commands, but does not use or store this information. Only InnoDB tables 

support checking of foreign key constraints including 

ON DELETE CASCADE

 and 

ON UPDATE CASCADE

.

Views

A view is a definition for a  virtual table  (virtual because there is no 

permanent allocation of storage space) which is assembled at reference time 

from selected rows and columns of one or more real tables. 

Views are useful for two main reasons: 

  They enable users to see data, from a generalized database design, in the 

form most convenient for their needs. 

  They may be used to secure the database by restricting users to just that 

data they need to know.

MySQL does not support views.

Subqueries

Subqueries are nested queries in a SQL statement (Example 1 3). They are 

not supported in MySQL 4.0. 

 Chapter 1. Introduction 

37