Table Designer

The table designer allows the creation and modification of database tables. Another method is the use of the SQL statements CREATE TABLE , ALTER TABLE and DROP TABLE.

The SQL source code can be viewed (but not modified) using Design / Show SQL. The ALTER TABLE command is always in the original 602SQL syntax using the designer (regardless of the 602SQL Server settings). This is different from the SQL standard (for details see ALTER TABLE).

The table designer is composed of two parts:

The top part is a grid of table columns containing the column name, type and other mandatory (string length) or common properties (default values, index type for this column and the NULLable flag).
The bottom part has a number of tabs that allow you to specify parameters for the select column or entire table.

Editing activities

Insert a new column at the end Insert the new column description at the last grid row.

Insert a new column at this position Set the grid on a position and select the Design / Insert Column. The inserted column is highlighted in green.

Edit column values Edit the values directly in the grid. The modified column is highlighted in blue. A name change is done by removing and the creating a new column. The designer allows you to do a data transfer from the old column to the modified column after the actual modification. This transfer can be modified on the Move column values tab.

Remove a column Select the desired column and run the Design / Delete Column action. If the column is being referred to outside the table definition (e.g. there is an index for this column), you may decide whether these references should be kept in the table or deleted along with the column. If you decide to keep them, you should create a new column of the same name.

Add an index A simple index (for one column) can be added directly to the grid, in the Index column. Indexes that are more complicated are created on the Indexes tab.

Define referential integrity The RI is defined in the Referential integrity tab on a child table.

Column description

Column name: Set the column name here. The column name can have a maximum of 30 characters, special characters and blank spaces.

Column type: Choose the data type or the user defined type as a domain from this box.

Length: For CHAR(n) and BINARY(n) types. Enter the maximum character count that can be inserted in the column. The maximum allowed length for strings is 4090 characters. The size is set in bytes for INTEGER types: 1 byte=TINYINT, 2 bytes=SMALLINT, 4 bytes=INT and 8 byte=BIGINT. The value range in the extent of 1 to 8 bytes is set for the NUMERIC type (not the maximum number count as in the SQL standard, however the standard notation is used by the designer when using the SQL statement CREATE or ALTER TABLE).

Default value: The value that will be used in each new record when the record is inserted that does not have a value specified. The value must be the same type as the column. It is allowed to use general expressions (in the server context, not the client context). Many times, a sequence for generating unique numbers is used. An overview of special default values can be found in the CREATE TABLE SQL statement description.

Index: You can create an index for each column. Choose one index type from the table:

Primary key Special unique index. Only one can exist in a table. 602SQL does not require it explicitly, however some external client programs will not working correctly without a primary key.

Unique Values in the index will be unique. The 602SQL Server will not allow you to insert duplicate values.

Non-unique Values will not be unique. Duplicity may occur.

-no index- An index will not be created for this column.

The offer in a combo contains the options without and with NULL value - details on a page Database table indexes.

NULLable?: Uncheck the checkbox if you wish to not allow inserting a record that will have an empty value in this column. This is generally used in combination with the default value.

Further parameters can be set on various tabs in the bottom window.

The parameters specific to 602SQL (outside SQL) are set on the Table properties window (menu command Design / Table properties). There is only one such property in the current version, Enable record-level privileges. This flag should be used if it is necessary to protect individual records against unauthorized access (see Individual record privileges). Only the author, administrator and the users with sufficient privileges will have access to modify the records in this table.

List of topics:

Table Designer Properties

Object Names in 602SQL

Database Objects Designers

Query Designer

Insert a new column at the end	Insert the new column description at the last grid row.
Insert a new column at this position	Set the grid on a position and select the Design / Insert Column. The inserted column is highlighted in green.
Edit column values	Edit the values directly in the grid. The modified column is highlighted in blue. A name change is done by removing and the creating a new column. The designer allows you to do a data transfer from the old column to the modified column after the actual modification. This transfer can be modified on the Move column values tab.
Remove a column	Select the desired column and run the Design / Delete Column action. If the column is being referred to outside the table definition (e.g. there is an index for this column), you may decide whether these references should be kept in the table or deleted along with the column. If you decide to keep them, you should create a new column of the same name.
Add an index	A simple index (for one column) can be added directly to the grid, in the Index column. Indexes that are more complicated are created on the Indexes tab.
Define referential integrity	The RI is defined in the Referential integrity tab on a child table.

Primary key	Special unique index. Only one can exist in a table. 602SQL does not require it explicitly, however some external client programs will not working correctly without a primary key.
Unique	Values in the index will be unique. The 602SQL Server will not allow you to insert duplicate values.
Non-unique	Values will not be unique. Duplicity may occur.
-no index-	An index will not be created for this column.