Reference Manual: Commands

Home

Reference Manual: Commands

Chapter 1 Commands

alter table

Examples

Example 1

Adds a column to a table. For each existing row in the table, Adaptive Server assigns a NULL column value:

alter table publishers 
add manager_name varchar(40) null

Example 2

Adds an IDENTITY column to a table. For each existing row in the table, Adaptive Server assigns a unique, sequential column value. Note that the IDENTITY column has type numeric and a scale of zero. The precision determines the maximum value (10 5 - 1, or 99,999) that can be inserted into the column:

alter table sales_daily
add ord_num numeric(5,0) identity

Example 3

Adds a primary key constraint to the authors table. If there is an existing primary key or unique constraint on the table, the existing constraint must be dropped first (see Example 5):

alter table authors
add constraint au_identification
primary key (au_id, au_lname, au_fname)

Example 4

Creates an index on authors; the index has a reservepagegap value of 16, leaving 1 empty page in the index for each 15 allocated pages:

alter table authors
add constraint au_identification
primary key (au_id, au_lname, au_fname)
with reservepagegap = 16

Example 5

Drops the au_identification constraint:

alter table titles 
    drop constraint au_identification

Example 6

Removes the default constraint on the phone column in the authors table. If the column allows NULL values, NULL is inserted if no column value is specified. If the column does not allow NULL values, an insert that does not specify a column value fails:

alter table authors
    replace phone default null

Example 7

Creates four new page chains for the titleauthor table. After the table is partitioned, existing data remains in the first partition. New rows, however, are inserted into all five partitions:

alter table titleauthor partition 5

Example 8

Concatenates all page chains of the titleauthor table, then repartitions it with six partitions:

alter table titleauthor unpartition
    alter table titleauthor partition 6

Example 9

Changes the locking scheme for the titles table to datarows locking:

alter table titles lock datarows

Example 10

Adds the not-null column author_type to the authors table with a default of primary_author:

alter table authors
    add author_type varchar(20)
    default "primary_author" not null

Example 11

Drops the advance, notes, and contract columns from the titles table:

alter table titles
    drop advance, notes, contract

Example 12

Modifies the city column of the authors table to be a varchar(30) with a default of NULL:

alter table authors
    modify city varchar(30) null

Example 13

Modifies the stor_name column of the stores table to be NOT NULL. Note that its datatype, varchar(40), remains unchanged:

alter table stores
    modify stor_name not null

Example 14

Modifies the type column of the titles table and changes the locking scheme of the titles table from allpages to datarows:

alter table titles
    modify type varchar(10)
    lock datarows

Example 15

Modifies the notes column of the titles table from varchar(200) to varchar(150), changes the default value from NULL to NOT NULL, and specifies an exp_row_size of 40:

alter table titles
    modify notes varchar(150) not null
    with exp_row_size = 40

Example 16

Adds, modifies, and drops a column, and then adds another column in one query. Alters the locking scheme and specifies the exp_row_size of the new column:

alter table titles
    add author_type varchar(30) null
    modify city varchar(30)
    drop notes
    add sec_advance money default 1000 not null
    lock datarows
    with exp_row_size = 40


	Expand Search