Database Reference
In-Depth Information
•
Disable foreign key checks
: In the export file, we can add
DROP
TABLE
statements. However, normally a table cannot be dropped if it is referenced
in a foreign key constraint. This option overrides the verification by adding
SET
FOREIGN_KEY_CHECKS=0
to the export file.
SQL compatibility mode:
•
This lets us choose the flavor of SQL that we
export. We must know about the system onto which we intend to import this
file. Among the choices are
MySQL 3.23
,
MySQL 4.0
,
Oracle,
and
ANSI
.
The options in
Structure
section are:
•
Add DROP TABLE / DROP VIEW
:
Adds a
DROP
TABLE
IF
EXISTS
statement
before each
CREATE
TABLE
statement, for example:
DROP
TABLE
IF
EXISTS
`author`;
. This way, we can ensure that the export file is executed on a
database in which the same table already exists, updating its structure but
destroying the previous table contents. A
DROP VIEW
is used for views.
Add IF NOT EXISTS
•
: Adds the
IF
NOT
EXISTS
modifier to
CREATE
TABLE
statements, avoiding an error during import if the table already exists.
Add AUTO_INCREMENT value
•
: Puts auto-increment information from
the tables into the export, ensuring that the inserted rows in the tables will
receive the next exact auto-increment ID value.
Enclose table and field names with backquotes
•
: Backquotes are the normal
ways of protecting table and field names that may contain special characters.
In most cases, it is useful to have them. However, backquote are not
recommended, if the target server (where the export file will be imported)
is running a MySQL version older than 3.23.6, which does not support
backquotes.
Add CREATE PROCEDURE / FUNCTION
•
: This includes all procedures
and functions found in this database, in the export.
Add into comments
•
: This adds information (in the form of SQL comments),
which cannot be directly imported, but which nonetheless is valuable and
human-readable table information. The amount of information here varies
depending on the relational system settings (see Chapter 11). In fact, with an
activated relational system, we would get the following choices:
Search WWH ::
Custom Search