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 ( ) and navigate to .
To modify a database object, right-click the object name in the Database tool window ( ) 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.
Item | Description | |
|---|---|---|
Name | Sets a name for the schema. If available, click the Auto Generate button ( | |
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 |
| 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.
| |
) for the name to be generated automatically.
, 
, 
, 
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.
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 (
) 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 () on the toolbar.By default, to edit the object in the Objects editor, double-click it. Alternatively, select it and click the Edit button (
) on the toolbar, or press F4.To select the object for editing by clicking it in the tree, click the Edit On Selection button (
) on the toolbar.To delete an object, select it in the tree and click the Remove button (
) on the toolbar.
For more information about tables, refer to the Tables topic.
Icon | Item | Shortcut | Description |
|---|---|---|---|
| Add | Alt+Insert | Create a new object. |
| Remove | Alt+Delete | Delete the selected object. |
| Edit | F4 | Open selected object in the object editor. |
| Edit On Selection | Toggle between opening the object in object editor on selection and by double-clicking it. | |
| Back | Ctrl+Alt+Left | Previous object. |
| 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.
Item | Description | |
|---|---|---|
Name | Sets a name for the table. If available, click the Auto Generate button ( | |
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 |
| 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.
Item | Description | |
|---|---|---|
Name | Sets a name for the column. If available, click the Auto Generate button ( | |
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.
Item | Description | |
|---|---|---|
Name | Sets a name for the key. If available, click the Auto Generate button ( | |
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 |
| 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.
Item | Description | |
|---|---|---|
Name | Sets a name for the key. If available, click the Auto Generate button ( | |
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 |
| 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.
Item | Description | |
|---|---|---|
Name | Sets a name for the index. If available, click the Auto Generate button ( | |
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 |
| 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. | |
Item | Description | |
|---|---|---|
Name | Sets a name for the check. If available, click the Auto Generate button ( | |
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.
Item | Description | |
|---|---|---|
Name | Sets a name for the virtual column. If available, click the Auto Generate button ( | |
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.
Item | Description | |
|---|---|---|
Name | Sets a name for the virtual key. If available, click the Auto Generate button ( | |
Target Table | Sets the target table. | |
Columns pane |
| 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 (
).To close the dialog and open your SQL script in a query console, click the Open query in console button (
).
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 (
). Alternatively, right-click the tab name and select Pin Tab.
- Use context menu of table objects
You can also use manage the objects using their 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.
Views
For more information about views, refer to the Views topic.
Item | Description | |
|---|---|---|
Name | Sets a name for the view. If available, click the Auto Generate button ( | |
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 |
| 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.
| |
Users and roles
For more information about users and roles, refer to the Users and roles topic.
Item | Description | |
|---|---|---|
Name | Sets a name for the user or role. If available, click the Auto Generate button ( | |
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 |
| 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.
| |
Virtual views
For more information about virtual views, refer to the Views topic.
Item | Description | |
|---|---|---|
Name | Sets a name for the virtual view. If available, click the Auto Generate button ( | |
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.
| |