alter table (core)

Description

This command provides these functions:

Adds new columns
Adds, changes, or drops constraints
Partitions or unpartitions an existing table

Syntax

Transact-SQL Syntax

alter table [database.[owner].]table_name

{add column_name datatype
[default {constant_expression | user | null}]
 {[{identity | null}]
 | [[constraint constraint_name]
 {{unique | primary key}
 [clustered | nonclustered]
 [with {fillfactor | max_rows_per_page} = x]
 [on segment_name]
 [references [[database.]owner.]ref_table
[(ref_column)]
 | check (search_condition)}]}...
 {[, next_column]}...

| add {[constraint constraint_name]
 {unique | primary key}
 [clustered | nonclustered]
 (column_name [{, column_name}...])
 [with {fillfactor | max_rows_per_page} = x]
 [on segment_name]
 | foreign key (column_name [{, column_name}...])
 references [[database.]owner.]ref_table
[(ref_column [{, ref_column}...])]
 | check (search_condition)}

| drop constraint constraint_name

| replace column_name
default {constant_expression | user | null}

| partition number_of_partitions

| unpartition}

ODBC Syntax

ALTER TABLE base_table_name

{ADD column_identifier datatype

|ADD(column_identifier datatype[,column_identifier datatype]...)

|DROP[COLUMN]column_identifier[CASCADE|RESTRICT]}

Parameters

null: specifies that you should assign a NULL value when a value is not provided during an insertion.

table_name: is the name of the table to be changed.

column_name: is the name of a column to be added.

datatype: is any of the system datatypes except Bit. If the transformation mode is passthrough, the datatype is expressed as an ODBC datatype.

next_column: indicates that you can include additional column definitions separated by commas, using the same syntax described for a column definition.

Examples

Example 1

alter table publishers

add manager_name varchar(40) null

This adds the manager_name column to the publishers table. For each existing table row, a NULL value is assigned to the new column.

Usage

ASE/CIS sends the alter table command to the DirectConnect server as a language event.
These are not supported:
- add constraint
- drop constraint
- replace column name
- partition | unpartition
Transformation adds parentheses when the add column option includes more than one column.