Oracle 5.0 Reference Manual Download

Page: 46 / 3232

MySQL Differences from Standard SQL

using an

AUTO_INCREMENT

column and either the

LAST_INSERT_ID()

[961]

SQL function

or the

mysql_insert_id()

C API function. See

Section 12.13, “Information Functions”

, and

Section 20.6.6.37, “

mysql_insert_id()

”

You can generally code around the need for row-level locking. Some situations really do need it, and

InnoDB

tables support row-level locking. Otherwise, with

MyISAM

tables, you can use a flag column

in the table and do something like the following:

UPDATE

tbl_name

SET row_flag=1 WHERE id=ID;

MySQL returns

for the number of affected rows if the row was found and

row_flag

wasn't

in the

original row. You can think of this as though MySQL Server changed the preceding statement to:

UPDATE

tbl_name

SET row_flag=1 WHERE id=ID AND row_flag <> 1;

1.8.5.4. Foreign Keys

The

InnoDB

storage engine supports checking of foreign key constraints, including

CASCADE

DELETE

, and

ON UPDATE

. See

Section 14.2.3.4, “

InnoDB

and

FOREIGN KEY

Constraints”

For storage engines other than

InnoDB

, MySQL Server parses the

FOREIGN KEY

syntax in

CREATE

TABLE

statements, but does not use or store it. In the future, the implementation will be extended

to store this information in the table specification file so that it may be retrieved by

mysqldump

and

ODBC. At a later stage, foreign key constraints will be implemented for

MyISAM

tables as well.

Foreign key enforcement offers several benefits to database developers:

• Assuming proper design of the relationships, foreign key constraints make it more difficult for a

programmer to introduce an inconsistency into the database.

• Centralized checking of constraints by the database server makes it unnecessary to perform these

checks on the application side. This eliminates the possibility that different applications may not all
check the constraints in the same way.

• Using cascading updates and deletes can simplify the application code.

• Properly designed foreign key rules aid in documenting relationships between tables.

Do keep in mind that these benefits come at the cost of additional overhead for the database server to
perform the necessary checks. Additional checking by the server affects performance, which for some
applications may be sufficiently undesirable as to be avoided if possible. (Some major commercial
applications have coded the foreign key logic at the application level for this reason.)

MySQL gives database developers the choice of which approach to use. If you don't need foreign
keys and want to avoid the overhead associated with enforcing referential integrity, you can choose
another storage engine instead, such as

MyISAM

. (For example, the

MyISAM

storage engine offers

very fast performance for applications that perform only

INSERT

and

SELECT

operations. In this case,

the table has no holes in the middle and the inserts can be performed concurrently with retrievals. See

Section 8.7.3, “Concurrent Inserts”

If you choose not to take advantage of referential integrity checks, keep the following considerations in
mind:

• In the absence of server-side foreign key relationship checking, the application itself must handle

relationship issues. For example, it must take care to insert rows into tables in the proper order, and
to avoid creating orphaned child records. It must also be able to recover from errors that occur in the
middle of multiple-record insert operations.

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands