CLion 2023.3 Help

Create Table and Modify Table Dialogs

In CLion, Create and Modify dialogs have the same fields and functionality for a given database object. This way, you can edit the same fields when you create a new object and when you edit an existing one.

  • To create a database object, right-click a data source, a database, or a schema name in the Database tool window ( View | Tool Windows | Database) and navigate to New | <database_object>.

  • To modify a database object, right-click the object name in the Database tool window ( View | Tool Windows | Database) and select Modify <database_object>. Alternatively, click the object name and press Ctrl+F6.

This topic describes the Create and Modify dialogs for the following database objects: schemas, tables, columns, keys, foreign keys, indexes, checks, virtual columns, virtual foreign keys, views, users and roles, and virtual views.

Schemas

For more information about schemas, refer to the Schemas topic.

the Create schema dialog

Item

Description

Name

Sets a name for the schema.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the schema.

For more information about how to view comments in Database tool window, refer to View options.

Owner

Defines the owner of the schema.

Grants pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Left part

Defines the grantee.

Right part

Defines the permissions.

For more information about granting permissions to users and roles, refer to the Users and roles topic.

Preview

The pane under the Preview separator shows the statement or statements that CLion will run to achieve the result you have specified using the GUI.

You can both use this pane as a preview of the auto-generated SQL script, and to write and edit statements yourself.

  • To select the settings to run the script with, click the Settings button (the Settings icon).

  • To close the dialog and open your SQL script in a query console, click the Open query in console button (the Open query in console icon).

Tables and table objects

The table structure editor on the upper-left side of the dialog lets you manage the objects within the table. In the objects editor on the upper-right side, you can edit properties of table's objects. The preview pane on the lower part of the dialog shows the SQL script that CLion will run when you close the dialog by clicking the OK button.

Parts of the Create and Modify dialogs
  1. Table structure editor.

  2. Objects editor.

  3. SQL script preview pane.

Table structure editor

In the table editor section, you can edit the table's structure by adding and deleting the objects.

  • To add a new objects, in the toolbar, click the Add button (the Add icon) and select the object. Alternatively, select the family node of the object that you want to add, or any of the existing objects of that kind, and click the Add button (the Add icon) on the toolbar.

  • By default, to edit the object in the Objects editor, double-click it. Alternatively, select it and click the Edit button (the Edit icon) on the toolbar, or press F4.

    To select the object for editing by clicking it in the tree, click the Edit On Selection button (the Edit On Selection icon) on the toolbar.

  • To delete an object, select it in the tree and click the Remove button (the Remove icon) on the toolbar.

For more information about tables, refer to the Tables topic.

Table structure editor in Create and Modify dialogs

Icon

Item

Shortcut

Description

the Add icon

Add

Alt+Insert

Create a new object.

the Remove icon

Remove

Alt+Delete

Delete the selected object.

the Edit icon

Edit

F4

Open selected object in the object editor.

the Edit On Selection icon

Edit On Selection

Toggle between opening the object in object editor on selection and by double-clicking it.

the Back icon

Back

Ctrl+Alt+Left

Previous object.

the Forward icon

Forward

Ctrl+Alt+Right

Next object.

Objects editor

In the upper-right pane of the dialog, you can edit properties of the object.

The following table objects are available for editing in the objects editor:

For more information about tables, refer to the Tables topic.

Create a new table

Item

Description

Name

Sets a name for the table.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the table.

For more information about how to view comments in Database tool window, refer to View options.

Owner

Defines the owner of the table.

Grants pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Left part

Defines the grantee.

Right part

Defines the permissions.

For more information about granting permissions to users and roles, refer to the Users and roles topic.

For more information about columns, refer to the Columns topic.

Add a column

Item

Description

Name

Sets a name for the column.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the column.

For more information about how to view comments in Database tool window, refer to View options.

Data Type

Sets a data type for the column.

Not Null

Defines whether a cell value can be null.

Default Expression

Sets a default expression for the column cell values.

For more information about keys, refer to the Primary keys topic.

Create a primary key

Item

Description

Name

Sets a name for the key.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the key.

For more information about how to view comments in Database tool window, refer to View options.

Primary

Makes the key a primary one.

Columns pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Column Name

Sets which column to use in current table.

Target Name

Sets which column to use in target table.

For more information about foreign keys, refer to the Foreign keys topic.

Create a foreign key

Item

Description

Name

Sets a name for the key.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the key.

For more information about how to view comments in Database tool window, refer to View options.

Target Table

Sets the target table.

Columns pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Column Name

Sets which column to use in current table.

Target Name

Sets which column to use in target table.

Deferrable

Defines whether a foreign key checking can be postponed until the end of the transaction. Otherwise, it will be checked after every statement.

Initially Deferred

Sets a foreign key to be checked only at the end of the transaction. Otherwise, it will be checked after every statement.

On Delete

Sets a referential action to be performed once a referenced row is deleted.

On Update

Sets a referential action to be performed once a referenced row is updated.

For more information about indexes, refer to the Indexes topic.

Create an index

Item

Description

Name

Sets a name for the index.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the index.

For more information about how to view comments in Database tool window, refer to View options.

Unique

Defines whether the index is unique.

Columns pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Column Name

Sets which column to use in current table.

Order

Defines in which order the data is stored.

Collation

Defines how the data is sorted and compared.

Create a check

Item

Description

Name

Sets a name for the check.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the check.

For more information about how to view comments in Database tool window, refer to View options.

Predicate

Defines an expression used to specify the allowed cell values.

For more information about virtual columns, refer to the Virtual columns topic.

Create a virtual column

Item

Description

Name

Sets a name for the virtual column.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Expression

Defines the expression that is used to calculate values.

The expression used to calculate values for virtual column is stored in external-data-<data_source_name>.xml. You can select another name for the XML file and other place to store this file. To change or see the path to the XML document, open data source settings by pressing Shift+Enter, click the Options tab and see the Virtual objects and attributes field.

The virtual foreign key relation is stored in external-data-<data_source_name>.xml. You can select another name for the XML file and other place to store this file. To change or see the path to the XML document, open data source settings by pressing Shift+Enter, click the Options tab and see the Virtual objects and attributes field.

For more information about virtual foreign keys, refer to the Virtual foreign keys topic.

Create a virtual foreign key in the Modify dialog

Item

Description

Name

Sets a name for the virtual key.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Target Table

Sets the target table.

Columns pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Column Name

Sets which column to use in current table.

Target Name

Sets which column to use in target table.

Preview

The pane under the Preview separator shows the statement or statements that CLion will run to achieve the result you have specified using the GUI.

You can both use this pane as a preview of the auto-generated SQL script, and to write and edit statements yourself.

  • To select the settings to run the script with, click the Settings button (the Settings icon).

  • To close the dialog and open your SQL script in a query console, click the Open query in console button (the Open query in console icon).

Object editor tabs in the Modify dialog

Productivity tips

Pin the object tabs

To pin the tab with the current object, hover over the tab name and click the Pin Tab icon (the Pin icon). Alternatively, right-click the tab name and select Pin Tab.

Objects editor tabs in the Modify dialog
Use context menu of table objects

You can also use manage the objects using their context menu.

Manage objects in a Modify dialog using a context menu
View similar objects list

The Modify dialog can display objects of the same kind in a list. To see that list, in the left pane of the dialog, double-click on the family node.

Table with similar database objects in a family node of a Modify dialog

Views

For more information about views, refer to the Views topic.

Create a view

Item

Description

Name

Sets a name for the view.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the view.

For more information about how to view comments in Database tool window, refer to View options.

Owner

Defines the owner of the view.

Source Text

Defines the statement that is used to create the view.

Grants pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Left part

Defines the grantee.

Right part

Defines the permissions.

For more information about granting permissions to users and roles, refer to the Users and roles topic.

Preview

The pane under the Preview separator shows the statement or statements that CLion will run to achieve the result you have specified using the GUI.

You can both use this pane as a preview of the auto-generated SQL script, and to write and edit statements yourself.

  • To select the settings to run the script with, click the Settings button (the Settings icon).

  • To close the dialog and open your SQL script in a query console, click the Open query in console button (the Open query in console icon).

Users and roles

For more information about users and roles, refer to the Users and roles topic.

Create a user

Item

Description

Name

Sets a name for the user or role.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Comment

Adds a comment to the user or role.

For more information about how to view comments in Database tool window, refer to View options.

Create DB

Determines whether the user can create new databases or not.

Grants pane

the Add icon, the Remove icon, the Up icon, the Down icon

Use these buttons to add items, remove them, and move them up and down the list.

Left part

Defines the grantee.

Right part

Defines the permissions.

For more information about granting permissions to users and roles, refer to the Users and roles topic.

Preview

The pane under the Preview separator shows the statement or statements that CLion will run to achieve the result you have specified using the GUI.

You can both use this pane as a preview of the auto-generated SQL script, and to write and edit statements yourself.

  • To select the settings to run the script with, click the Settings button (the Settings icon).

  • To close the dialog and open your SQL script in a query console, click the Open query in console button (the Open query in console icon).

Virtual views

For more information about virtual views, refer to the Views topic.

Create a virtual view

Item

Description

Name

Sets a name for the virtual view.

If available, click the Auto Generate button (the Auto Generate icon) for the name to be generated automatically.

Query

Defines the SQL statements that is used to obtain a result set.

The SQL statement of virtual view is stored in external-data-<data_source_name>.xml. You can select another name for the XML file and other place to store this file. To change or see the path to the XML document, open data source settings by pressing Shift+Enter, click the Options tab and see the Virtual objects and attributes field.

Preview

The pane under the Preview separator shows the statement or statements that CLion will run to achieve the result you have specified using the GUI.

You can both use this pane as a preview of the auto-generated SQL script, and to write and edit statements yourself.

  • To select the settings to run the script with, click the Settings button (the Settings icon).

  • To close the dialog and open your SQL script in a query console, click the Open query in console button (the Open query in console icon).

Last modified: 11 February 2024