On the first page of the EDB table adding wizard (figure 41-1) the user should first of all choose whether a new table will be created in the data source or a new view based on existing tables.
Figure 41-1. EDB table adding wizard, step 1 for a base table
If the user selects the radio button Base table (CREATE TABLE) then at the given step he should enter the name of the table being created and, perhaps, to choose the table's owner (if the notion of owner is supported by the data source).
At the second step of table adding wizard for a base table a set of columns (fields) of the table being created should be specified (figure 41-2).
Figure 41-2. EDB table adding wizard, step 2 for a base table
To add a new column one should firstly push the button Add and then enter name and type of the column in corresponding fields. The type of column is to be selected from a list containing data types supported in the SQL language. For some types the size of data is to be specified (or the default value is accepted) and for the type DECIMAL the scale should be also entered, i.e. the number of digits in fractional part. Adding of a column is completed by pushing the button Save. Buttons Up and Down allows changing the columns order in EDB records and the button Delete perform deleting of a previously added column selected in the list.
On the third step of the wizard the user can define the primary key of the table (figure 41-3). The notion of primary key refers to a column or a set of columns which values determine a table record in a unique way. The presence of a primary key is not obligatory but is highly desirable for DBMS to work with the table. If it seems to be impossible to select columns for a unique key in a natural way then a special column of type COUNTER is often appended to the table, and the DBMS will automatically assign unique values for each new record in this column.
Figure 41-3. EDB table adding wizard, step 3 for a base table
For the table “Building certificates” it is most natural to accept the building's inventory number as the primary key because this number must be unique.
If the user has checked the box Primary key presence then he or she is to specify a column of several columns which will play a part of a primary key. For that, it is necessary to move names of that columns from the list of available columns to the list of key columns using buttons 
and 
, and, in the case of several columns, to set up their order using buttons Up and Down.
It is also possible to specify a name for the primary key being created. In terms of SQL language such a field is labeled as Constraint name. This name does not matter for ObjectLand's work with the table. It can be used by experienced users who prefer to write SQL statements manually.
At the next step the user has the possibility to define additional unique keys for the table in addition to the primary key.
Figure 41-4. EDB table adding wizard, step 4 for a base table
Additional unique keys, similarly to the primary key, are not obligatory, however they can increase effectiveness of work with the EDB table. In particular, unique keys allows detecting violations of uniqueness of values of a column or a set of columns on the level of data source server.
For instance, for the table “Building Certificates” a unique key can consist of street code, building number and the extra part of the building number. Each of these columns taken separately can have equal values for different buildings, but the combination of three columns specifies a building in a unique way.
For each unique key, as for the primary key, a name can be defined in the field Constraint name. This name can be used later in SQL expressions defined for working with the table.
At the next step of the wizard foreign keys of the table are defined (figure. 41-5).
Figure 41-5. EDB table adding wizard, step 5 for a base table
These keys can also consist of one of several columns but their purpose is quite different. Foreign keys are used in operations of joining tables or setting a reference table in a query. For instance, there is the column “Material Code” in the table “Building Certificates”, and this column is used for joining with the table “Reference of Materials”. Therefore when creating the table it is desirable to declare the column “Material Code” as a foreign key. That allows the server to handle the table join in a more effective way.
Definition of a foreign key starts by pushing the button Add in the upper part of the window. Then one should select in the pane Foreign Table a table to be joined with via the key. The foreign table should belong to the same EDB as the table being added. For some of the database servers specifying the table's owner can be required. It is possible also to enter a name for the foreign key in the field Constraint name. Next, after pushing the button Add in the lower part of the window, it is required to select a column of the table being created in the field located below the list Column of key. In the field below the list Foreign table column a column is to be selected in the foreign table which will correspond to the previously selected key column of the table being added. In the given example the column “Code” of the foreign table “Reference of Materials” corresponds to the column “Material Code” of the table “Building Certificates”. After pushing the button Save the pair of selected columns is inserted in corresponding lists. If the foreign key consists of several columns, it is required to push the button Add in the lower part of the window again and to repeat the selection of a pair of columns. In case several foreign keys are to be created, one should push the button Add in the upper part of the window again and repeat all the procedure of defining a foreign key.
At the next step of the wizard the user can define one or several indexes for the table (figure 41-6).
Figure 41-6. EDB table adding wizard, step 6 for a base table
An index definition consist of a name of a column or set of several column names which values will be often used for search in the table. The database server generates auxiliary data (the index) which help to significantly accelerate the search.
The table's primary key and/or additional unique keys are often used as indexes. However it is not necessary. Moreover, the values of the index columns are not necessarily unique. The presence of several table records having equals values in all the index columns is allowed. However, if in a specific case the values are necessarily unique (which is always the case when indexing on a primary or unique key), then the box Uniqueness is to be checked because this allows increasing effectiveness of the search.
Definition of an index starts by pushing the button Add, next the user should enter name of the index (it is required, in contrast with the names of keys), select appropriate table columns in the list Available columns and transfer them in the list Index columns. For each of these columns the sorting order when building index (ascending or descending) is to be specified. The button Add transfers the index name and all the list of selected columns to the list of indexes located in the upper part of the window. If more indexes are required, the button Add is to be pushed again.
For not to confuse the notions of a key and of an index, it should be taken into consideration that keys are used by the database server mostly for checking the conditions or uniqueness of records, so they belong to the constraints imposed to the table. In the contrary, indexes serves to accelerate search in the table, and there are additional data related to index, created and maintained by the server.
Finally, after table columns, keys and indexes are defined, one can proceed to the actual creation of the table in the data source. Before that, at next to last step of the wizard, the user can inspect the SQL expression with table creation parameters specified at previous steps (figure 41-7).
Figure 41-7. EDB table adding wizard, step 7 for a base table
The upper part of the window contains a list of SQL statements which are to be sent to the data source server to produce creation of the desired table. The list necessarily contains the table creation statement 'CREATE TABLE' and one statement 'CREATE INDEX' for each index defined by the user. The lower part of the window contains complete text of the statement selected in the list.
If the user is experienced in SQL language, he or she can, at his own risk, modify the statement's text to add some functionality not supported yet by ObjectLand. For that, it is enough to check the box Change generated SQL expression, which makes the text editable. The fact of modifying the expression is indicated in the column Modified alongside of the description of the corresponding statement.
After pushing the button Execute the data source server performs the generated SQL statements and in that way it creates in the EDB a new table accessible in ObjectLand. Then the wizard passes to the last step at which the message is displayed describing the result of the operation of table creating. At the figure 41-8 a situation is illustrated when the operation is completed with an error, i.e. the table was not created.
Figure 41-8. EDB table adding wizard, step 8 for a base table
In the window's pane the information on the error's character is shown. In the given case, there was a syntax error in the text of the 'CREATE TABLE' statement which may result from the manual modification of the statement.
In the case of an error the user can get back to that wizard step at which the error occurred, and repeat the table creation operation after correction of the error.
 |  |
