Adding a Column

Add a column to a base table or global temporary table.

Prerequisites

Database Version	Table Column Privileges
SAP Sybase IQ 15.3 and 15.4	Create a column without a primary key or unique constraint – you must have one of: DBA authority Both ALTER permission on the table You own the underlying table Create a column with a primary key or unique constraint – you must have one of: DBA authority Both ALTER and REFERENCE permission on the table You own the underlying table
SAP Sybase IQ 16.0	Create a column without a primary key or unique constraint – you must have one of: ALTER ANY OBJECT system privilege CREATE permission on the dbspace along with one of the following: ALTER ANY TABLE system privilege ALTER permission on the underlying table You own the underlying table Create a column with a primary key or unique constraint – you must have one of: ALTER ANY OBJECT system privilege CREATE permission on the dbspace along with one of the following: ALTER ANY TABLE system privilege Both ALTER and REFERENCE permission on the underlying table You own the underlying table

Database Version

Table Column Privileges

SAP Sybase IQ 15.3 and 15.4

Create a column without a primary key or unique constraint – you must have one of:

DBA authority
Both ALTER permission on the table
You own the underlying table

Create a column with a primary key or unique constraint – you must have one of:

DBA authority
Both ALTER and REFERENCE permission on the table
You own the underlying table

SAP Sybase IQ 16.0

Create a column without a primary key or unique constraint – you must have one of:

ALTER ANY OBJECT system privilege
CREATE permission on the dbspace along with one of the following:
- ALTER ANY TABLE system privilege
- ALTER permission on the underlying table
- You own the underlying table

Create a column with a primary key or unique constraint – you must have one of:

ALTER ANY OBJECT system privilege
CREATE permission on the dbspace along with one of the following:
- ALTER ANY TABLE system privilege
- Both ALTER and REFERENCE permission on the underlying table
- You own the underlying table

The SAP Sybase IQ resource is authenticated and running.
In a multiplex configuration, the coordinator node is running.

Task

In the Perspective Resources view, select the resource, and select Resource > Administration Console.
In the left pane, expand IQ Servers > Schema Objects > Tables, and then select Tables, Global Temporary Tables, or Proxy Tables.
Select a table from the right pane and either:
- Click the arrow to the right of the name and select Properties, or
- From the Administration Console menu bar, select Resource > Properties.
The Table Properties view appears.
In the left pane, click Columns and in the right pane, click New
The Create Column Wizard appears.
On the Columns Name page, enter a unique name for the column and indicate whether the column is to be a primary key. Click Next.

On the Data Type page, specify:

Option	Description
Built-in type	Choose a predefined data type for the column. Integers, character strings, and dates are examples of predefined data types. For some of these types, you can specify size and scale. Size – Length of string columns, or the total number of digits to the left and right of the decimal point in the result of any decimal arithmetic for numeric columns. For numeric columns, the size is also called the PRECISION value. Scale – Minimum number of digits after the decimal point when an arithmetic result is truncated to the maximum PRECISION value. Units – Unit corresponding to the data type's size. Possible units include: bits, bytes, characters, or digits. For CHAR and VARCHAR data types, you can specify the unit as bytes or characters.
Domain	Choose a domain. A domain is a named combination of built-in data types, default value, check condition, and nullability.
Compress values	Compress column values. If a column is compressed, database server activities such as indexing, data comparisons, and statistics generation may be slightly slower if they involve the compressed column because the values must be compressed when written, and decompressed when read. This option is not available for certain built-in types.
Maintain BLOB indexes for large values	Maintain BLOB indexes for large values. Only character, binary, and bit types support this option.

Click Next.

On the Value page, specify:

Option	Description
No default or computed value	Select this option if the column is not a computed value and has no default value.
Default Value	Select this option if the column has a default value. If the column is based on a domain, this setting inherits the domain's default value (if any), but you can override the value for the column. Choosing the Default value option enables the User-defined and System-defined options. User-defined – Type a custom value (string, number, or other expression) for the default value. If you have based the column on a domain, you can retain the domain's default value (if any), or override it for the column. Literal string – Specify whether the default value for the column should be treated as a literal string. By default, this option is selected for character columns and domains with a character base type. You do not need to enclose default text in single quotes, and escape embedded single quotes or backslashes in the string. System-defined – Lets you select a predefined value (for example, current date) for the default value. Select a value. If you have based the column on a domain, you can retain the domain's default value (if any) or override it for the column.
Partition size	When you select global autoincrement, the domain of values for that column is partitioned. Each partition contains the same number of values. For example, if you set the partition size for an integer column in a database to 1000, one partition extends from 1001 to 2000, the next from 2001 to 3000, and so on.
Computed Value	Define a computed value for the column. A computed column derives its values from calculations of values in other columns. Type an expression in the text box to describe the relationship between the other columns and the value that appears in the computed column.

Click Next.

On the Constraints page, specify:

Option	Description
Values can be null	Select this option if null values are allowed in the column. If the column is based on a domain, you can retain the domain's nullability or override it for the column.
Values cannot be null	Select this option to allow duplicate values, but not allow null values in this column.
Values cannot be null and must be unique	Select this option if values in the column cannot be null and must be unique.

Click Next.
(IQ main store base tables only.) On the Placement page, select a dbspace from the list. Only those dbspaces in read-write mode, and those dbspaces for which the user has permissions, appear in the list. Click Next.
On the Comment page, add an optional, descriptive comment. Comments help you organize your database.
Click Finish.
The new column appears in the Column Definitions list.
Do one of the following:
- Click New to add more columns to the table.
- Click Apply to update any column changes to the Table Properties view and remain in the Column Properties view.
- Click OK to update any column changes to the Table Properties view and exit the Column Properties view.
Clicking OK or Apply in the Column Properties view updates any column changes to the Table Properties dialog. It does not save the changes to the database. Your changes are not saved until you click OK or Apply on the Table Properties view.
Do one of:
- Click OK to update any changes to the database and exit the properties view.
- Click Apply to update any changes to the database, but remain in the properties view.
- Click Cancel to cancel any changes not updated to the database and exit the properties view.