34,333
edits
Changes
→Defining Default Values During Table Creation
== Defining Default Values During Table Creation ==
Default values during table creation allow values to be specified for each column to be used when a value is not specifically defined when rows are inserted into to a database.
Default values are specified using the ''DEFAULT'' keyword in the CREATE TABLE statement. For example, the following SQL statement specifies a default value for the ''sales_quantity'' column:
<pre>
CREATE TABLE sales
{
sales_number int NOT_NULL,
sales_quantity int NOT_NULL DEFAULT 1,
sales_desc char(20) NOT_NULL,
PRIMARY KEY (sales_number)
) ENGINE=MyISAM;
</pre>
If no quantity is defined when a new row is inserted, MySQL will insert the default value of 1.
== MySQL Database Engine Types ==
Each of the example table creation statements used so far in this chapter has included an ''ENGINE='' definition. MySQL ships with a number of different database engines, each of which have particular strengths. Using the ''ENGINE='' directive it is possible to select which database engine is used on a per table basis. MySQL database engines currently available are:
* '''InnoDB'' - The InnoDB was introduced with MySQL version 4.0 and is categorized as a ''transaction-safe'' database. A transaction safe database engine ensures that all database transactions are 100% completed, and rolls back any partially completed transactions (for example as the result of a server or power failure). This ensures that a database is never subject to partially completed data updates. A drawback of the InnoDB database engine is that it does not support full-text seraching.
* '''MyISAM''' - The MySQL MyISAM database engine is a high-performance engine with support for full-text searching. This performance and functionality comes at the price of not being transaction safe.
* ''MEMORY''' - The MEMORY database engine is equivalent to the MyISAM database in terms of functionality with the exception that all data is stored in memory as opposed to being disk based. This makes the engine extremely fast. The transient nature of data in memory makes this engine more sauitable for temporary table storage.
Engine types may be mixed withion a database, for example some tables may use the InnoDB engine, whilst others use MyISAM. If no engine is specified during table creation, MySQL will default to MyISAM for that table.
To specify an engine type for a table simply place the appropriate ''ENGINE='' definition after the table columns has been defined. The following example specified the ''MEMORY'' engine:
<pre>
CREATE TABLE tmp_orders
{
tmp_number int NOT_NULL,
tmp_quantity int NOT_NULL,
tmp_desc char(20) NOT_NULL,
PRIMARY KEY (tmp_number)
) ENGINE=MEMORY;
</pre>