Table Designer

The Table Designer lets you create and modify relational, XML, or object tables. It also lets you create, modify, and drop relational table columns, constraints, and indexes.

This section covers the following topics:

• Creating Tables in Oracle Developer Tools

• Starting the Table Designer

• Using the Table Designer

• XML Tab

• Object Tab

Creating Tables in Oracle Developer Tools

You use the Table Designer to create relational, XML, or object tables. The Oracle Output Window displays the SQL statements that the Table Designer executes, as well as any errors that occurred while saving data. You can view or edit a table's data by using Oracle Data Window.

Starting the Table Designer

Start the Table Designer by using one of the following methods:

• If you are creating a new table, in Server Explorer, right-click the Tables node and from the menu, select New Relational Table, New XML Table, or New Object Table.

• If you are modifying an existing table or one of its columns, constraints, or indexes, in Server Explorer, right-click the node for that table under the Tables node, and from the menu, select Design.

The Table Designer appears similar to the following:

Description of the illustration table_designer1.gif

Using the Table Designer

The Table Designer has the following components:

• Main Window

• One of the following depending on table type, respectively Relational, XML, or Object. The Relational table is the default.

  • Columns Tab

  • XML Tab

  • Object Tab

• Constraints Tab

• Indexes Tab

In general, to use the Table Designer, make the modifications you need for the table. If you are adding a column, constraint, or index to the table, click Add and then make the necessary modifications in the Details pane. If you are dropping a table component, select the component and click Remove.

Main Window

The main window for Table Designer is as follows:

Control	Description
Schema name	Select from the list of available schemas in which to create the table.
Table Name	Enter the name of the table you want to create. If you are replacing an existing table, this box displays the table's name.
Table type	Read-only. Type of the table: Relational, Object or XML. The type of table determines the tabs displayed in the Table Designer: Relational tables: Columns tab, Constraints tab, Indexes tab, and Storage tab XML tables: XML tab and Storage tab Object Tables: Object tab, Constraints tab, Indexes tab, and Storage tab
Preview SQL	Displays the CREATE TABLE SQL command code in a pop-up dialog box and in the output window. Read-only.
Save	Saves any changes made to the table to the database. Alternatively, you can click the Save button in the Visual Studio toolbar or select Save from the File menu.

Columns Tab

The Columns tab, which only appears for relational tables, displays the following controls:

Control	Description
Columns	Lists the available columns and their data types with precision and whether or not they are null. The Column Properties pane displays detailed information about the currently selected column. If the name of a column or data type is too long to appear, you can hold the mouse over it to read the full name. A small key icon is displayed next to the primary key column. Click the up and down arrows to change the order of the columns.
Add	Adds a new column to the table and selects it. By default, a new column is named COLUMNn and its data type is NUMBER. To modify the new column, make the appropriate changes in the Column Details pane.
Remove	Drops the selected column from the table.
Properties	Displays the following details about the currently selected column: Name: If you are creating a new column, enter its name here after you click the Add button. If you had selected an existing column from the Available Columns list box, its name displays here. To enter a case-sensitive name, enclose the name in double quotation marks. Not Null: Specifies whether the column allows NULL values. Data type: Specifies the data type of the column. Available choices are simple Oracle data types and user-defined types. The following components depend on the type of data type you select: Default: Specifies the default value of the column. Specify this value as an expression. For example, to specify the default value of a VARCHAR2 column as "MANAGER", enter 'MANAGER' (enclosed in single quotes) into this field. Precision: For numeric types, specifies the precision of the column or the total number of digits. A zero value indicates the minimum precision allowed by the database. For interval types, specifies the precision in terms of fractional seconds, days, or years. Scale: Specifies the column's scale, or the number of digits to the right of the decimal point. A zero value indicates the minimum scale allowed by the database. Length semantic: Specifies the length semantic for the column. Available choices are: Default: The database length semantic Byte: Byte length semantic Char: Character length semantic Size: Specifies the data type size.
	Create as Ref: Store a User Defined Type as a pointer (REF) to an Object Table rather than inline. Store as: Specifies how to store an XMLType column. Available choices are: Clob: Store XML data as a Clob Object Relational: Store XML data as a Object Relational Type Binary: Store XML data as binary data. (Available in Oracle Database 11.2.01 or later) Use XML Schema: Specifies whether or not to use an XML schema. This is automatically checked (mandatory) for XML stored as an Object Relational type. For more information on registering XML Schemas with Oracle Database, see XML Schema Designer. XML Schema Owner: Specifies the owner of the XML Schema.
	XML schema name: Specifies the XML schema for the column, an XML schema to which the column must conform. Available choices are as follows: None All XML schemas that you can access If you specify an XML schema, you must specify an XML Root Element. XML root element: Specifies the XML root element. Binary Storage Options: Specifies the storage options for binary XML storage. Available in Oracle Database 11.2.0.1 or later. Available choices are: None Allow Any Schema: (Only available when Use XML Schema is not checked) Allow Non-Schema: Allow All: Only available when Use XML Schema is not checked)
	Identity Column: Specifies that a column is an identity column. Only available in Oracle 12.1 database or later, for columns of a numeric type. This may only be modified on new columns or existing identity columns. There may only be one identity column for each table.
	Identity Column Properties: This is a modifiable list of identity column properties. Available properties are as follows: Generation Type: Specifies in which cases the identity column value is generated by database. Available choices are: ALWAYS: The database will always generate the value. BY DEFAULT: The database will generate a value when one is not provided. BY DEFAULT ON NULL: The database will generate a value when NULL is provided. During initial column creation, all three options are available. For editing an existing identity column, the options are ALWAYS and either DEFAULT or BY DEFAULT ON NULL. BY DEFAULT ON NULL is only available if the column was initially created with that Generation Type. Minimum Value: Specifies the minimum value that the database will generate for this identity column. Maximum Value: Specifies the maximum value that the database will generate for this identity column. Interval: Specifies the interval between the values that the database will generate for this identity column. Start With: Specifies the starting value to be generated by the database for this identity column. For new columns, enter a value. For existing columns, enter a value or choose Limit Value from the drop down list. If Limit Value is chosen, it will lock the table and find the maximum sequence value found in the table (for increasing sequences) or find the minimum sequence value found in the table (for decreasing sequences). Allow Cycle: Indicates whether or not the database cycles around (that is, starts over) and continues generating values when the minimum or maximum value is reached. Order Value: Guarantees that values are generated in the order of request. This may be needed when using Real Application Clusters. Cache Values: Specifies if values are preallocated by the database in memory for better performance. Cache Size: Specifies how many values are preallocated in the cache.

Constraints Tab

Use the Constraints tab to edit the constraints of the table. Its controls are as follows:

Control	Description
Constraints	Lists the constraints on the table and their constraint types. The Constraint Details pane displays detailed information about the currently selected constraint. Add: Adds a new constraint to the table and selects it. By default, a new constraint is named CONSTRAINTn and by default it is a unique constraint. To modify the new constraint, make the appropriate changes in the Constraint Details pane. Remove: Removes the selected constraint.
Constraint Properties	Displays the following details about the currently select constraint: Name: If you are creating a new constraint, enter its name here after you click the Create button. If you have selected an existing constraint from the Available Constraints list box, its name appears here. To enter a case-sensitive name, enclose the name in double quotation marks. Type: Select from the following constraint types: Unique Primary key Check Foreign key UDT columns Ref Deferrable: Specifies whether the constraint is deferrable, that is, whether the constraint's checking can be deferred until a transaction that the constraint affects has completed. If you deselect Deferrable, the constraint must be valid at the time the new or altered table is committed, or the table creation or modification will fail. Enabled: Specifies whether the constraint is enabled or disabled. The default is enabled. Validate: Specifies whether the constraint is validated. A validated constraint is guaranteed to hold for all rows of the table, even if the constraint is disabled. Execution: Establishes the default checking behavior for constraints that are deferrable. It specifies either of the following: Immediate: Oracle will check this constraint at the end of each subsequent SQL statement. If you do not specify Initially at all, the default setting is Initially Immediate. Deferred: Oracle will check this constraint at the end of subsequent transactions.
	The following components depend on the type of constraint you select: Primary Key Columns/Unique Key Columns: Specifies the columns on which the primary key or unique key is based. Available choices are the table's columns. When adding a key, use the drop down list to choose the column. Add (Primary/Unique Key): Adds a new column to the list of primary/unique keys. Remove (Primary/Unique Key): Removes the selected column from the list of primary/unique keys. Using Index (Primary/Unique Key): Specifies the index to use the constraint. Select Auto to have the database automatically generate an index. Table (Foreign Key): Specifies the table where the foreign key constraint is located. Constraint (Foreign Key): Specifies the foreign key constraint. Association (Foreign Key): Specifies the referenced column and the local column on which the key operates. When adding a new constraint, click on the local column to choose from the drop-down list. On Delete (Foreign Key): Specifies how Oracle Database automatically maintains referential integrity if you remove a referenced primary or unique key value. Select from the following: No action: Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table. Cascade: Oracle removes dependent foreign key values. Set null: Oracle converts dependent foreign key values to NULL.
	Condition (Check Constraint): Enter the condition to be checked. For example: JOB_ID IS NOT NULL. Column/Attribute (Ref Constraint): REF constraints let you describe the relationship between a column of type REF and the object it references. You can specify the name of a REF column of an object table or relational table or specify an embedded REF attribute within an object column of a relational table. Scope (Ref Constraint): Select a table name that will be used to restrict the scope of references in the REF column. To specify this clause, scope_table must be in your own schema or you must have SELECT privileges on scope_table or SELECT ANY TABLE system privileges. You can specify only one scope table for each REF column. Scope/With Rowid (Ref Constraint): Select this checkbox to store the rowid along with the REF value in the column or attribute specified in the REF constraint. Storing the rowid with the REF value can improve the performance of dereferencing operations, but will also use more space. Default storage of REF values is without rowids. OID Based on Primary Key (Primary Key): For a primary key constraint on an object table, this selection will cause the primary key to be used as the object identifier.

Indexes Tab

Use the Indexes tab to create or modify the table's indexes. Its controls are as follows:

Control	Description
Indexes	Lists the indexes on the table, their index types, and affected columns. The index type can be Bitmap or B-tree. The affected columns are in a comma-delimited list. The Index Details pane displays detailed information about the currently selected index. Add: Adds a new index to the table and selects it. By default, a new index is named INDEXN and by default it is a B-Tree index. To modify the new index, make the appropriate changes in Index Details. Remove: Removes the selected index.
Index Properties	Displays the following details about the currently selected index: Name: If you are creating a new index, enter its name here after you click the Add button. If you have selected an existing index from the Available Indexes list box, its name appears here. To enter a case-sensitive name, enclose the name in double quotation marks. Type: Specifies the type of the index. Available choices are: B-Tree: Create the index as a normal index. Bitmap: Creates the index with a bitmap for each distinct key, rather than indexing each row separately. Unique: Specifies the index as unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Reverse: Stores the bytes of the index block in reverse order, excluding the rowid. Index keys: Specifies the index keys, the columns or expressions to be indexed. You can also specify whether each key will be indexed in ascending or descending order. To index a column, select the column from the drop-down list in the Key column. Remember that the order of the columns is important. To index an expression, type the expression into the Key column. To specify the order of the index key, select Ascending or Descending from the corresponding drop-down list in the Order column. Indexes on character data are created in ascending or descending order of the character values in the database character set. Add: Add a key to the list of Index Keys. Remove: Remove the selected Index Key from the list.

XML Tab

Use the XML tab to specify the properties of an XML table. Its components are as follows:

Control	Description
Store As	Specifies how to store the XML. Available choices are: CLOB: Store XML data as a Clob. Object Relational: Store XML data as a Object Relational Type Binary: Store XML data as binary data. (Available in Oracle Database 11.2.01 or later)
Use XML Schema:	Specifies whether or not to use an XML schema. This is automatically checked (mandatory) for XML stored as an Object Relational type. For more information on registering XML Schemas with Oracle Database, see XML Schema Designer.
XML Schema Owner:	Specifies the owner of the XML Schema.
XML schema name	Specifies the XML schema for the column, an XML schema to which the column must conform. Available choices are as follows: None All XML schemas that you can access If you specify an XML schema, you must specify an XML Root Element.
XML root element	Specifies the XML root element.
Binary Storage Options:	Specifies the storage options for binary XML storage. Available in Oracle Database 11.2.0.1 or later. Available choices are: None Allow Any Schema: (Only available when Use XML Schema is not checked) Allow Non-Schema: Allow All: (Only available when Use XML Schema is not checked)

Object Tab

Use the object tab to specify the properties of an object table. Its components are as follows:

Control	Description
Object Type	Lists the object types that you can select.
Object Substitutable At All Levels	Enables substitution at all levels. This means that row objects corresponding to subtypes can be inserted into this object table. Substitution is disabled for all embedded nested tables and arrays.