Tables wizard (MySQL)

From RapidSQL
Jump to: navigation, search

Go Up to MySQL object wizards

The MySQL Table Wizard lets you create a basic table definition.

To create a new table using a wizard:

  1. Open a creation wizard for a table. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Tables - Properties

When creating or editing a table, this tab/panel lets you work with the following properties:

Note: Prior to working with MySQL table properties, you should have a detailed understanding of MySQL table creation options. For access to MySQL documentation, see Accessing Third Party Documentation.
Setting Description

Table Name

Provide a unique name of up to 64 characters.

Storage Type

Lets you select a storage engine value of MyISAM, InnoDB, BerkeleyDB, ISAM, MRG_MyISAM, HEAP, or MEMORY.

Row Format

Lets you select a row format of DEFAULT (returns the default value if there is one), FIXED (each row is stored with a fixed number of bytes), DYNAMIC (data records have variable length), or COMPRESSED (each record is compressed separately).

Create via SELECT

When you create a table using a SELECT command, the individual columns take their data types from the SELECT command, and don’t have to be declared explicitly. Attributes, however, are not carried over.

Unique Key Violations

When you try to insert or update a row that causes a violation, you can choose to IGNORE the violation and continue processing the next row. Or, you can choose REPLACE, which is equivalent to an insert statement if a violation occurs. The row will be replaced with the new data.

Default Character Set

This is the character set for a language or alphabet.

Default Collation

The collation encodes the rules governing character used for a language (like Greek) or an alphabet.

Auto-increment

You can specify the initial value used for the auto-increment sequence. This is possible only for MyISAM tables and for InnoDB tables built in MySQL versions 5.0.3 or greater. For InnoDB tables built in versions prior to 5.0.3, you can manually insert a dummy row with a value one less than the desired value after creating the dummy table; you then delete the dummy row.

Comment

Enter a descriptive comment.

Min Rows and Max Rows

Note that if you set the value for either parameter at 0, MySQL removes the setting. MySQL will take care of the row settings in this case.

Average Row Length

The average length (compressed or uncompressed) of table rows in the table space.

Pack Keys

This setting is only enabled for a Storage Type of MyISAM. It lets you specify a PACK KEYS option value of 0 (keys packing disabled), 1 (smaller indexes), or DEFAULT (pacl long columns only).

Check Sum

This setting is only enabled for a Storage Type of MyISAM. When selected the CREATE TABLE statement is issued with a CHECKSUM = 1 option. When deselected, the CREATE TABLE statement is issued with a CHECKSUM = 0 option.

Delay Key Write

This setting is only enabled for a Storage Type of MyISAM. When selected, the CREATE TABLE statement is issued with a DELAY_KEY_WRITE = 1 option. When deselected, the CREATE TABLE statement is issued with a DELAY_KEY_WRITE = 0 option.

Tables - Columns

When creating or editing a table, this tab/panel lets you modify the table’s column setup, as follows:

  • Use the Add Column button to add the columns to the table. After providing a Name for a new column, you can select a Type and modify other Datatype properties corresponding to the type you selected. When selected, the Allow Nulls property corresponds to a CREATE TABLE with a NULL attribute, while unselected it corresponds to a NOT NULL attribute. When available the Default Value property corresponds to a DEFAULT attribute.
  • Modify a column definition by selecting the column from the column list and modifying values under Column Attributes.
  • Delete a column definition by selecting the column from the column list and clicking Delete.
  • Use the arrow buttons to change the position of a selected column.

Tables - Indexes

When creating or editing a table, this tab/panel lets you build an associated PRIMARY KEY, UNIQUE KEY, FULL TEXT, or SPATIAL clause that will be included with the CREATE TABLE statement.

  • Use the Insert a New Index button to add an index. In the Index field, type a name for the index, select a Constraint Type of PRIMARY KEY, UNIQUE KEY, FULL TEXT, or SPATIAL, and select an Index Type of BTREE, HASH, or RTREE as appropriate to the constraint type. Under Specify Columns in Index, select the column or columns that are to make up the index.
  • Modify an index definition by selecting the index from the list and modifying the Index name, Constraint Type, Index Type or the columns making up the index.
  • Delete an index by selecting the index from the list and clicking the Remove button.

Tables - Foreign Keys

When creating or editing a table, this tab/panel lets you work with foreign keys for a table:

Note: This tab/panel is only available for a table with a Storage Type property value of INNODB.
  • Use the Insert a New Foreign Key button to add a foreign key. Provide a Foreign Key Name, select the referenced database and table from the Ref. Database and Ref. Table dropdowns, and select On Delete and On Update actions of NONE, CASCADE, DEFAULT, SET NULL, NO ACTION, or RESTRICT. Under Main Table, select the foreign key column and then under Referenced Table, select the column that the foreign key is to reference.
  • Modify a foreign key definition by selecting the foreign key from the list and changing the name, referenced database or table, delete or update actions, or the column specifications.
  • Delete a foreign key by selecting the key from the list and clicking the Remove the selected foreign key button.

Tables - MERGE Tables

When creating or editing a table, this tab/panel lets you work with a collection of identical MyISAM tables that are to be used as a single table.

Note: This tab/panel is only available for a table with a Storage Type property value of MRG_MyISAM.
  • Use the New button to add a MyISAM table to the collection, selecting a table from the associated dropdown.
  • Use the Delete button to remove a selected MyISAM table from the collection.
  • Use the arrow buttons to change the ordering position of a selected table.