to prevent problems with automatically generated SQL queries that use constructs such as the following, where we automatically insert the value of the payment for payment : 

UPDATE account SET credit=credit-payment 

Consider about what happens if payment  has a negative value such as -1 : 

UPDATE account SET credit=credit--1 

credit--1  is a valid expression in SQL, but --  is interpreted as the start of a comment, part of the expression is discarded. The result is a statement that has a completely different meaning than intended: 

UPDATE account SET credit=credit 

The statement produces no change in value at all. This illustrates that permitting comments to start with -- can have serious consequences. 

Using our implementation requires a space following the --  for it to be recognized as a start-comment sequence in MySQL Server. Therefore, credit--1  is safe to use. 

Another safe feature is that the mysql  command-line client ignores lines that start with -- . 

## 1.7.3 How MySQL Deals with Constraints 

MySQL enables you to work both with transactional tables that permit rollback and with nontransactional tables that do not. Because of this, constraint handling is a bit different in MySQL than in other DBMSs. We must handle the case when you have inserted or updated a lot of rows in a nontransactional table for which changes cannot be rolled back when an error occurs. 

The basic philosophy is that MySQL Server tries to produce an error for anything that it can detect while parsing a statement to be executed, and tries to recover from any errors that occur while executing the statement. We do this in most cases, but not yet for all. 

The options MySQL has when an error occurs are to stop the statement in the middle or to recover as well as possible from the problem and continue. By default, the server follows the latter course. This means, for example, that the server may coerce invalid values to the closest valid values. 

Several SQL mode options are available to provide greater control over handling of bad data values and whether to continue statement execution or abort when errors occur. Using these options, you can configure MySQL Server to act in a more traditional fashion that is like other DBMSs that reject improper input. The SQL mode can be set globally at server startup to affect all clients. Individual clients can set the SQL mode at runtime, which enables each client to select the behavior most appropriate for its requirements. See Section 5.1.11, “Server SQL Modes” . 

The following sections describe how MySQL Server handles different types of constraints. 

## 1.7.3.1 PRIMARY KEY and UNIQUE Index Constraints 

Normally, errors occur for data-change statements (such as INSERT  or UPDATE ) that would violate primary-key, unique-key, or foreign-key constraints. If you are using a transactional storage engine such as InnoDB , MySQL automatically rolls back the statement. If you are using a nontransactional storage engine, MySQL stops processing the statement at the row for which the error occurred and leaves any remaining rows unprocessed. 

MySQL supports an IGNORE  keyword for INSERT , UPDATE , and so forth. If you use it, MySQL ignores primary-key or unique-key violations and continues processing with the next row. See the section for the 

