Creating a table and defining a column

 

A table is a basic database object that is used to store information. After you create a table, you can define columns, create indexes, and add triggers and constraints.

When you are creating a table, 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. A null value 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 how to create a table to maintain information about the current inventory of a business. The table contains 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 null values.

To create the table, follow these steps:

1. From iSeries™ Navigator, 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. The New Table window is shown.

   1. Specify INVENTORY_LIST as the table name.

   2. Select SAMPLELIB in the Schema field.

   3. Select System-generated in the System table name field.

   4. Optional: Specify a description in the Text field.

      

6. Define a column for the new table. Click the Columns tab and click the Add button. The New Column window is shown.

   1. Type ITEM_NUMBER in the Column name field.

   2. 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. For this example, leave this space as the default, System-generated.

   3. Select CHARACTER as the data type.

   4. 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.

   5. Leave the Encoding option as the default, Data type default.

   6. Optional: Specify a description for the column in the Text field.

   7. Type a column heading in the Heading fields. The heading is the label that shows at the top of the column for displaying or printing. The heading is limited to 60 characters, 20 per line.

   8. Clear the Nullable check box. This ensures that a value must be placed in this column for the row insertion to be successful.

   9. In the Default value field, type 0.

   10. Click OK to create the table.

       

The new table INVENTORY_LIST is shown.

• Defining additional columns on a table
  After you create a table, you can still add new columns to the table.

• Creating the supplier table
  Assume that later you need a second table. This table contains information about suppliers of inventory items, which items they supply, and the cost of the item from that supplier.

• Copying column definitions
  Copying column definitions saves you from defining columns in multiple tables by sharing the same definitions across the tables.

• Inserting information into a table
  After you create a table and define its columns, you can insert data into your table.

• Viewing the contents of a table
  You can display the contents of your tables and views. When viewing the contents of a table, you cannot make changes to the table. To make changes to a table, edit the table.

• Changing information in a table
  You can use iSeries Navigator to change the values in the columns of a table. The value that you give must be valid for that column.

• Deleting information from a table
  You can delete information from a single column in a row or delete the row entirely. If a column requires a value, you cannot delete it without deleting the entire row.

• Copying and moving a table
  You can copy or move tables from one schema or system to another. By copying a table, you create more than one instance of the table. By moving a table, you transfer the table to its new location while removing the instance from its former location.

 

Parent topic:

Getting started with iSeries Navigator