Adding Columns to a Table (Add Option)

Home | Previous Page | Next Page The Table Option > Creating or Altering a Table >

Adding Columns to a Table (Add Option)

To add a new column to a table, you define a new line in the Schema Editor that appears below the dashed line. When you create or alter the columns of a table, the Schema Editor issues prompts to assist you.

Important:

Before you use the Add option from the ALTER TABLE menu, you must position the highlight in the Schema Editor to indicate where you want to insert the new column or columns. To move the highlight within the displayed columns, use the up and down arrow keys. To scroll more of the column list onto the screen, use the Screen option on the menu. When you select the Add option, the highlighted line moves down to make an empty line for the new column.

The Schema Editor progresses from left to right, completing one horizontal line of description for each column, with the name of the column at the left. Use the right arrow key to move the highlight to each field. To accept the default entry for each field, press RETURN or an arrow key.

As you finish one column, the cursor moves to the next line down, so that you can type another column name. Thus, the columns that make up the table are listed vertically.

You can change or bypass any field entry in a line before you move to the next line in either of the following ways:

Use the left arrow key to move back to a field that you passed on the current line.
Press the Interrupt key to cancel a prompt without inserting a value into the current (highlighted) field.

After you move the cursor to another line, you must use the Modify option on the CREATE TABLE menu to change your entry, as Modifying Columns (Modify Option) describes.

Column Name

The Add option on the CREATE TABLE (or ALTER TABLE) menu places the cursor on an empty line and displays the ADD COLUMN NAME prompt. Type the name of the column after the ADD COLUMN NAME prompt and press RETURN. You can assign any name, as long as you follow the identifier syntax guidelines described in the IBM Informix: Guide to SQL Syntax.

DB–Access enters the specified name under Column Name, as Figure 47 shows.

Figure 47. The ADD COLUMN NAME Screen with Column Name Entered

ADD COLUMN NAME >> 
Enter column name.  RETURN adds it.  INTERRUPT returns to CREATE/ALTER menu.

---- Page 1 of 1 ---- mydata@mydbserv  ---------- Press CTRL-W for Help ----

 Column Name                     Type             Length   Index Nulls

  customer_num

Column Data Type

The ADD TYPE menu displays various data types, as Figure 48 shows.

Figure 48. The ADD TYPE Menu for Defining Column Data Types

ADD TYPE clients :   Char   Numeric  Serial  Date  Money  date-Time ...
Permits any combination of letters, numbers, and punctuation.

---- Page 1 of 1 ----- mydata@mydbserv ---------- Press CTRL-W for Help ----

 Column Name                     Type            Length   Index Nulls

 customer_num

To select the data type for the column, type the first capitalized letter of the data type, using either uppercase or lowercase letters or the SPACEBAR to highlight it and then press RETURN.

Important:

Use the spacebar to move to your choice. Use the arrow keys to control cursor movement in the lower part of the screen.

Dynamic Server

The CREATE TABLE menu provides options for built-in data types. To define a column with one of the extended data types, such as smart large objects, user-defined (opaque) data types, or a collection data type, use the SQL menu to enter and run a CREATE TABLE statement.

End of Dynamic Server

If you select one of the following data type categories from the ADD TYPE menu, DB–Access displays one or two submenus for that category.

ADD TYPE Category	Data Type Submenu	Additional Submenu
Numeric	Integer
	Smallint
	Decimal
	Float	Smallfloat or Float
Char	Char (press `C` to select)
Char	Nchar (press `N` to select)
Variable-length	Varchar	Varchar (press `V` to select)
	Varchar	Nvarchar (press `N` to select)
	Text or Byte	Table
	Text or Byte	Blobspace

Tip:

Although some data types described above are not included in the menu mode, you can use any data types in interactive, non-menu mode.

Locale Character Data

If you use character data in a default locale, select Char for fixed-length data or Varchar if the table will have varying-length entries in that column.

Global Language Support

If you use a nondefault locale, select Nchar for fixed length or Nvarchar for varying length.

End of Global Language Support

Large Object Storage Location

If you select VARIABLE-LENGTH TEXT or BYTE data type, perform one of the following actions to indicate where that large-object data should reside:

Select Table to store the full data directly in the column.
Extended Parallel Server

Always select Table for databases that reside on Extended Parallel Server.

End of Extended Parallel Server
Select Blobspace to store the actual TEXT or BYTE data in a large-object space (blobspace). The table column then holds the blobspace location.
DB–Access displays the SELECT BLOBSPACE screen, as Figure 49 shows. Use the arrow keys to choose a blobspace from the alphabetical list or type the blobspace name at the top of the screen.
Figure 49. The SELECT BLOBSPACE Screen for Storing Variable-Length Data
```
SELECT BLOBSPACE >>
Select a blobspace with the Arrow Keys, or enter a name, then press Return

----- Page 1 of 1 ------ mydata@mydbserv ------- Press CTRL-W for Help ------

 cust_blob 
```

Data Length or Range

If you select any of the following data types for the column, a new ADD screen appears. Enter the appropriate information in the Length field.

Data Type: Length or Range
Char: Enter length (the default is 20).
Nchar: Enter length (the default is 20).
Numeric: For the fixed-point form of the DECIMAL type, enter the precision and scale (the default is 16, 2). For the floating-point form of the DECIMAL type, enter the precision only.
Serial: Enter the starting number (the default is 1).
Money: Specify a length (the default is 16, 2).
Datetime: Specify first to last datetime qualifiers.
Interval: Specify first to last interval qualifiers.
Varchar: Specify a maximum length (from 1 to 255 bytes) and a minimum space (from 0 to 255 bytes).
Nvarchar: Specify a maximum length (from 1 to 255 bytes) and a minimum space (from 0 to 255 bytes).

Column Index

DB–Access can construct only a nonclustered, ascending B-tree column index. Select the Yes option to create this type of index with the ADD INDEX menu, as Figure 50 shows.

Figure 50. The ADD INDEX Menu

ADD INDEX     clients :  Yes   No   
Specifies that this column will NOT have an index.

----- Page 1 of 1 ----- mydata@mydbserv ----------- Press CTRL-W for Help ----

 Column Name                     Type            Length   Index Nulls

 customer_num                    Serial            101

DB–Access displays an ADD DUPLICATES screen. Press RETURN or the Y key to allow duplicate values, and the word Dups appears in the Index field. Press the N key to prevent duplicate values. The word Unique appears in the Index field.

DB–Access displays the ADD FILL FACTOR PERCENTAGE screen, as Figure 51 shows.

If you do not want to index the values in this column or if you want any other type of index, such as an R-tree index, select the No option. You must create an R-tree index directly with SQL.

Column Index Fill Factor

Use the ADD FILL FACTOR PERCENTAGE screen, as Figure 51 shows, to set the fill-factor percentage when you create an index on a single column. The index column has fill factor and Unique or Dups abbreviated to U or D.

Figure 51. The ADD FILL FACTOR PERCENTAGE Screen

ADD FILL FACTOR PERCENTAGE >> 
Enter the fill factor percentage. RETURN adds it.

----- Page 1 of 1 -------personnel ------------- Press CTRL-W for Help -----

 Column Name                     Type            Length   Index   Nulls

empl_num                         Integer                   U
70%    No
last_name                        Char             20      D 90%   No
insurance                        Integer                  Dups    Yes
ss_num                           Integer                  Unique  No

Important:

You can only set a fill-factor value when you create a new index. You can modify the fill factor through the Modify option on the CREATE TABLE menu. However, you cannot alter it through the ALTER TABLE menu after the table for the index is created.

Enter any positive value to a maximum of 100. A value less than 1 or greater than 100 results in an error.

If you press RETURN without entering a value, the index will have the fill-factor percentage set in the database server ONCONFIG file. If ONCONFIG has no fill-factor setting, the index will have the default fill-factor value of 90 percent.

Null Value Permission

Specify whether the column allows null values on the ADD NULLS menu, as Figure 52 shows.

Figure 52. The ADD NULLS Menu

ADD NULLS     clients :   Yes   No  
Permits null values in this column.

---- Page 1 of 1 ----- mydata@mydbserv --------- Press CTRL-W for Help ----

 Column Name                     Type            Length   Index Nulls

 customer_num                   Serial            101     Unique

Select Yes to allow null values in the column or No to force the column to always have a non-null value.

To add another column definition to the table or return to the CREATE TABLE menu, press Interrupt, an arrow key, or RETURN.