Choose your database:
AnySQL
MySQL
MS SQL Server
PostgreSQL
SQLite
Firebird
Oracle
SQL Anywhere
DB2
MaxDB

Subscribe to our news:
Partners
Testimonials
Fayvor Love: "Sweet! Finally a clean-looking ERD tool that understands the mysql data types, with forward and reverse engineering".
Adam Allen: "I just have to say WOW!!! I'm so glad that you guys have developed something that is so useful for MySql. I am able to graphically view my tables, and I don't even have to think about my queries because I can visually design them. I just stumbled upon this, and I'm already very excited about it".

More

Add your opinion

SQL Maestro for MySQL online help

Prev Return to chapter overview Next

Specifying table options

All fields below are optional, i.e. it is not obligatory for you to fill them.

 


 

 

The field Name contains the name of the table being created as it was set on the previous step.

 

Owner

You can specify here the name of the MySQL server user that will own the new table, or leave this field blank to use the default user (namely, the user executing the command). By default, only the owner of an object can perform various operations with the object. In order to allow other users to operate it, privileges must be granted. (However, users that have the superuser attribute can always access any object.)

 

Comment

Set the optional text to describe the new table.

 

Engine

Defines the type of the new table.

MyISAM

The new binary portable table handler that is replacing ISAM.

 

InnoDB

Transaction-safe tables with row locking and foreign key support (since MySQL version 3.23.44 only).

 

Berkeley_db

Transaction-safe tables with page locking.

 

ISAM

The original table handler. It is not supported in the latest versions of MySQL.

 

MEMORY(HEAP)

The data for this table is only stored in memory.

 

MRG_MyISAM

A collection of MyISAM tables used as one table.

 

GEMINI

Table type, developed by NuSphere Company. It is not supported in the latest versions of MySQL.

 

FEDERATED

The storage engine accesses data in tables of remote databases rather than in local tables.

 

EXAMPLE

It  is a stub engine that does nothing. Its purpose is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.

 

CSV

The engine stores data in text files using comma-separated values format.

 

BLACKHOLE

The BLACKHOLE storage engine acts as a "black hole" that accepts data but throws it away and does not store it.

 

ARCHIVE

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

 

Connection (only for FEDERATED tables)

Specify here a connection string that indicates to the FEDERATED engine how to connect to the remote server.

 

The general form of the connection string is as follows:

 

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

 

Only mysql is supported as the scheme value at this point; the password and port number are optional.

 

Here are some example connection strings:

 

CONNECTION='mysql://username:password@hostname:port/database/tablename'

CONNECTION='mysql://username@hostname/database/tablename'

CONNECTION='mysql://username:password@hostname/database/tablename'

 

The use of Connection for specifying the connection string is non-optimal and is likely to change in future. Keep this in mind for applications that use FEDERATED tables. Such applications are likely to need modification if the format for specifying connection information changes.

 

Insert method (Only for Mrg_MYISAM tables)

You can optionally specify an Insert method option if you want inserts for the Mrg_MYISAM table to take place in the first or last table of the UNION list. Use a value FIRST or LAST to cause inserts to be made in the first or last table, respectively. If you do not specify the option or if you specify it with a value NO, attempts to insert rows into the Mrg_MYISAM table result in an error.

 

Union tables  (Only for Mrg_MYISAM tables)

Creates a collection of identical MyISAM tables as one. Click the ... button to edit the list of union tables in the Union Tables window.

 

Collation

The clause is specifying the table character set.

 

Partitioning type (Since MySQL 5.1)

The possibility of partitioning was implemented in MySQL 5.1. It allows you to distribute portions of individual tables across a filesystem according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations.

Select the type of table partitioning from the drop-down list.

 

(none)

Use the clause to create a table  without partitioning.

 

Range

In this case, Expression shows a range of values using a set of VALUES LESS THAN operators. When using range partitioning, you must define at least one partition using VALUES LESS THAN. You cannot use VALUES IN with range partitioning.

VALUES LESS THAN can be used with either a literal value or an expression that evaluates to a single value.

 

List

List partitioning in MySQL is similar to range partitioning in many ways. In this case each partition also must be explicitly defined. The chief difference is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. Expression is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a value list, a comma-separated list of integers.

 

Hash

The partitioning is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly into which partition (Partition count) a given column value or set of column values is to be stored; with hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.

 

Linear hash

The partitioning type differs from regular hashing in that linear hashing utilizes a linear powers-of-two algorithm whereas regular hashing employs the modulus of the hashing function's value.

 

Key

Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server.

 

Linear key

This type partitioning has the same effect on key partitioning as it does on hash partitioning, with the partition number being derived using a powers-of-two algorithm rather than modulo arithmetic.

 

 

Check sum

Maintains a checksum for all rows (makes table a little slower to update but makes it easier to find the corrupted tables).

 

Pack keys

Makes table indexes smaller. This usually makes updates slower and reads faster.

 

Delay key write

Delays key table updates until the table is closed.

 

Max rows

Minimum number of rows you plan to store in the table.

 

Min rows

Maximum number of rows you plan to store in the table.
 

Row format

Defines how the rows should be stored in the new table.

 

Avg row length

Use the option in case your table is larger than 4GB and your operating system supports large files among with Max rows options.

 

Auto increment (only to integer types)

Specify a special counter called the auto-increment counter that is used in assigning new values for the column.



Prev Return to chapter overview Next