Promotic

ALTER TABLE - statement of language DB_MSSQL

Description:
Change the structure of an existing database table. It is possible to add, delete or change the column.
Syntax:

ALTER TABLE table
  [ ADD column_name new_data_type [ NULL | NOT NULL ] [ PRIMARY KEY | UNIQUE ] [ IDENTITY ] [ DEFAULT default_expression ] [ CHECK check_expression ] [,...]
  [ DROP COLUMN column_name [, ...] ]
  [ ALTER COLUMN column_name new_data_type [ NULL | NOT NULL ]
  [, ...]
)

ALTER COLUMN Alter existing table.
ADD Adds new column.
DROP COLUMN Delete existing column.
table Name of the table.
column_name Name of the column.
new_data_type Column data type (bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, ntext, binary, varbinary, image).
NULL Enable using the NULL value in the column.
NOT NULL Disable using the NULL value in the column.
PRIMARY KEY Specifies column as the primary key of the table (UNIQUE by default but UNIQUE mustn't be stated).
UNIQUE Values in this column must be unique (writing the record with the value that already exists in this column, fails).
IDENTITY Column (often primary key) whose unique value is supplied by the SQL server itself on writing (only 1 such column is enabled in the table).
DEFAULT Definition of the default value, used on inserting new record, if the value for this column is not stated.
default_expression the default value is the result of this expression.
CHECK On inserting or editing the value its check will be performed. If the check fails, then the whole operation fails.
check_expression The check itself where the expression result specifies whether the operation is valid or not.
See also:
Example1:
Adds new column 'quality' (Integer).
ALTER TABLE data ADD quality smallint
Example2:
Adds new column 'quality' (Integer, mustn't be NULL, with the default value of 100).
ALTER TABLE data ADD quality smallint NOT NULL DEFAULT 100
Example3:
Delete existing column 'quality'.
ALTER TABLE data DROP COLUMN quality
Example4:
Change existing column 'quality' to greater data type.
ALTER TABLE data ALTER COLUMN quality int
PROMOTIC 9.0.27 SCADA system documentation MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r.o.