Create a table and define a column

A table is a basic database object that is used to store information. After you have created a table, you can define columns, create indexes, and add triggers and constraints by using the Table Properties window.

When you are creating a table, you need to understand the concepts of null value and default value. A null value indicates the absence of a column value for a row. It is not the same as a value of zero or all blanks. It means unknown. It is not equal to any value, not even to other null values. If a column does not allow the null value, a value must be assigned to the column. This value is either a default value or a user supplied value.

If no value is specified for a column when a row is added to a table, the row is assigned a default value. If the column is not assigned a specific default value, the column uses the system default value.

This example shows you how to create a table to maintain information about the current inventory of a business. It has information about the items kept in the inventory, their cost, quantity currently on hand, the last order date, and the number last ordered. The item number is a required value. It cannot be null. The item name, quantity on hand, and order quantity have user-supplied default values. The last order date and quantity allow the null value.

To create a table, follow these steps:

  1. In the iSeries™ Navigator window, expand the system that you want to use.
  2. Expand Databases and the database that you want to work with.
  3. Expand Schemas.
  4. Right-click SAMPLELIB and select New.
  5. Select Table > Table.
  6. On the New Table window, specify INVENTORY_LIST as the table name.
  7. Select SAMPLELIB in the Schema field.
  8. Select System-generated in the System table name field.
  9. Specify a description in the Text field (optional).
    screen capture of new table window
  10. Next, define a column for the new table. Click the Columns tab.
  11. Click the Add button.
  12. Enter ITEM_NUMBER in the Column name field.
  13. You can specify a short name in the Short column name field. If you do not specify a short name, the system automatically generates a name. If the column name is 10 characters or less, then the short name is the same as the column name. You can perform queries by using either column name. Just leave this space as the default, System-generated, for now.
  14. Select CHARACTER as the Data type.
  15. Specify a length of 6 for this column. For data types where the size is predetermined, the size is filled in and you cannot change the value.
  16. Leave the Encoding option as the default, Data type default.
  17. You can specify a description for the column in the Text field. This step is optional.
  18. Enter a column heading in the Heading fields. The heading is the label that appears at the top of the column for displaying or printing. You are limited to 60 characters, 20 per line.
  19. Deselect the Nullable option. This ensures that a value must be placed in this column in order for the row insert to be successful.
  20. In the Default value field, enter 0.
  21. Click OK to create the table.
    screen capture of new column window

The new table INVENTORY_LIST appears.

Parent topic: Get started with iSeries Navigator