SQLEditor Icon

Fields

Fields in SQLEditor represent columns within database tables. Fields have properties that can be edited using the inspector. They can also be moved between tables by dragging. Fields are always contained within tables except when being moved.

Tip:
You can also change the data type and some other settings of a field by using the context menu (Right Mouse Button/Control Click)

Fields support direct editing of the field name.

Columns in databases can have various options and SQLEditor supports most of the common options. Icons appear on fields for each option that is set.

The image below shows the table from the table example which has had a field added it to it.

The field shows all of the possible icons that fields can currently have in SQLEditor.

Most fields will not display all of these icons.

You can also add comments for tables and columns


Field Options

Option Icon Description
Foreign Key F Field is a foreign key and references another field
Primary Key P The field is part of a primary key
Default Value D The field has a default value set
Not Null N The field cannot be Null
Auto Increment A Field has an auto incrementing value (in supported databases)
Unsigned +/- The field is has the unsigned option set (for MySQL)
Zero Fill Z The field is has the Zero Fill option set (for MySQL)
Unique U The field has a unique constraint set on it
Indexed I The field is indexed
Binary b The field type is binary
Time Zone tz The field is of type timestamp and has a time zone set.
Field Comment ? The field has an attached comment


Field Inspector

The field inspector allows editing of fields, which in SQLEditor represent table columns

Name

This is the name of the field. SQLEditor may modify names you enter to ensure that the eventual SQL output is valid. In particular, you can't normally have two columns with the same name. If you try, then SQLEditor will add a number suffix to the name to ensure that each object is uniquely named

Type

This popup menu contains a list of the data types that are supported for the current dialect. You can also enter your own types here if you wish. If you enter a type with brackets (e.g VARCHAR(255) then the section within the brackets will be copied to the size/members field

Size/Members

This field is used to specify any parameters of the type. This usually means length, but might also be precision or decimal places displayed. If the type is a set type it might include the entries in the set. It's normally database specific

Default Value

The default value for the field. SQLEditor will normally try to quote values if appropriate, so you don't need to add quotes. If you want to manually specify the exact value and any quotes yourself, check the Don't quote default value option

Delete Field

Delete this field from its parent table

Add Field to Table

Adds another field to this field's parent table. Older versions of SQLEditor call this Add Sibling

Parent Table

The name of the parent table for this field. There is a small arrow to the right of the name which, if you click it, will select the parent table and show the inspector for it

Sub-panel menu

The field inspector has more controls than can fit in the window, so they are divided into sub-panels. The subpanel selector menu allows you to change which subpanel is displayed

The lower section of the field inspector has several sub-panels which can be switched between using the popup menu

Options sub-panel

By selecting Options from the field inspector sub-panel menu, the Options sub-panel is displayed. This allows editing of a field's foreign key settings. This panel allows editing basic settings on fields and is the default sub-panel visible.

Primary Key

Checked if this field should be part of a primary key. Checking this adds the current field to the table primary key, unchecking it will remove this field from the primary key. It won't affect whether other fields are part of the primary key. (It isn't an exclusive operation)

Unique

Checked if this field should have be UNIQUE. The result of this varies between databases, in MySQL it will normally export with the UNIQUE keyword, in other databases it may generate a UNIQUE index

If a field is the referenced target of a foreign key, then it is normally required to be either UNIQUE or be part of a primary key. SQLEditor will normally enforce this using UNIQUE, so this option will be checked and grayed out

Auto Increment

Checked if this field should have an auto-incremented value when a row is added. This varys between databases, in MySQL it adds the AutoIncrement keyword to the export, if the type is appropriate. SQLEditor will normally try not to add this option to the exported SQL unless it makes sense

Unsigned

Checked if this field has a datatype that should be UNSIGNED. This won't export anything unless the base data type supports UNSIGNED

Not Null

Checked if this field should be declared NOT NULL. If it should be nullable then it should be unchecked

Zero Fill

Checked if the field should be specified Zero Fill. (Mainly for MySQL use)

With Timezone

Checked if this field should be a time or date type and should be specified with a time zone. This will have an effect only if the base data type supports time zones

Don't Quote Default Value

If checked, the default type will be exported without adding any quote marks. This allows you to control how the default value is quoted without SQLEditor making changes. If it is unchecked then SQLEditor will quote the default value as appropriate and escape any quote marks that you include.

Indexed

Checked if this field should be indexed. This normally causes a new index to be added to the table at export. (Note that this index is only seen at export, it won't add an index to the parent table in SQLEditor)

prefix size

Allows you specify the size of the index prefix. An index prefix limits the size of an index to improve performance

Foreign key sub-panel

By selecting Foreign Key from the inspector panel popup menu, the foreign key sub-panel is displayed. This allows editing of a field's foreign key settings. Not all fields have foreign keys set, so the controls may be disabled. Also if you have a set a foreign key using a table level foreign key object, this section will also be disabled. (To edit a table level foreign key, click on the foreign key object itself within the table, not the fields that are referenced by it)

Foreign Key specification

The foreign key specification appears in a text field. It is not editable.

Remove

The Remove button removes the foreign key from the field

Cardinality

The two Cardinality popup menus allow editing of the realtion cardinality, the source end on the left, the target or destination end on the right. Options are "1", "0 or 1","1+","0,1+" and "Many". Changing the option here changes the diagram, but doesn't affect the SQL output in any current exporter

On Delete

The On Delete action popup allows you to specify the action that the database takes when values in a foreign key relationship are deleted. Options are currently "Database Default","Cascade","Restrict","Set Null","No Action" and "Set Default". Different exporters will support different variations of this.

On Update

The On Update action popup allows you to specify the action that the database takes when values in a foreign key relationship are updated. Options are currently "Database Default","Cascade","Restrict","Set Null","No Action" and "Set Default". Different exporters will support different variations of this.

Note sub-panel

By selecting Note from the field inspector sub-panel menu, the Note sub-panel is displayed. This allows editing of the field object's informational note or comment

The main section of this sub panel is a text field into which you can type. The text is then available to the SQL exporter as the comment text (in MYSQL for example). Some exporters may export this as part of the object SQL, others may include it as a block comment in the exported SQL.

Label sub-panel

By selecting Label from the field inspector sub-panel menu, the Label sub-panel is displayed. This allows editing of the field object's label

Label popup

Use the popup menu to choose a label to assign to this field, or to create and assign a new label

Background Color

The background color well indicates the color that will be used to draw the field. If a label is specified then the color will be the label's color, otherwise you can specify any color you like