ALTER TABLE

Purpose

Used to add, modify or delete table columns and constraints

Syntax

ALTER [IGNORE] TABLE [<database>!]<table>

ADD [COLUMN] (<column> <datatype> [<column constraints>] [,...]) | <table constraint>

| ALTER | MODIFY [COLUMN] <column> [SET DEFAULT <value> | DROP DEFAULT] | (<column> <datatype> [<column constraint>] [,...])

| CONSTRAINT (<column> SET <column constraint> <value> [,...]) | <table constraint>

| DROP [COLUMN] <column> | (<column> [,...]) | CONSTRAINT (<column> <column constraint> [,...]) | <table constraint>

| SET CHECK <condition> [ERROR <message>]

| MOVE (<column>,<position>)

| RENAME (<column>,<new column>)

| METADATA <JSON>

See Also

ADD TABLE, ALTER INDEX, ALTER VIRTUALTABLE, CONSTRAINTS, CREATE DATABASE, CREATE INDEX, CREATE TABLE, CREATE TRIGGER, CREATE VIRTUALTABLE, DATA TYPES, DELETE TRIGGER, DROP DATABASE, DROP INDEX, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, SELECT

Description

The ALTER TABLE command is used to add, modify or delete table columns and constraints and to rename columns. The ALTER TABLE statement automatically reloads the original data of the table back into the original columns. You must have ALTER privilege on the table. The table will be locked for EXCLUSIVE use during the operation.

Keywords	Description
database	The name of the database to which the table to be altered belongs. The '!' character must be included between the database name and the table name.
table	The name of the table to be altered.
ADD	This will insert one or more new columns into the table
COLUMN	Optional COLUMN keyword.
column	The name of the column to operate on.
datatype	The data type to be stored in the column, and the applicable length or precision.
column constraint	The column constraint.
table constraint	The table constraint.
ALTER | MODIFY	These are used to change existing column definitions and column and table constraints.
SET DEFAULT <expr>	Specify the DEFAULT column constraint to set a default value for the specified column. The <expr> must evaluate to the same data type as the target column. The column's value can subsequently be updated.
DROP DEFAULT	Remove the DEFAULT column constraint for the specified column.
DROP	This is used to delete existing column definitions and column and table constraints.
CONSTRAINT	This keyword is used if the constraint refers to a column.
SET	Precedes an existing column constraint whose value is being changed.
value	The new value for the specified column constraint.
SET CHECK <condition>	Specify CHECK table constraint. This validation is activated when an operation to insert, update or delete records in the table is called. The <condition> specified must evaluate to True (.T.) for the operation to succeed. If the <condition> evaluates to False (.F.) the operation is abandoned and the ERROR table constraint message is displayed. If the ERROR table constraint has not been defined, a default error message is displayed.
ERROR <message>	Specify ERROR table constraint. The <message> is the error message to be displayed if the CHECK table constraint evaluates to False (.F.).
MOVE	This is used to change the position of an existing column.
position	The position of the column in the structure.
RENAME	This is used to change the name of an existing column.
new column	The new name for the column.
METADATA	This is used to associate metadata with the table or specified column.
JSON	JSON encoded metadata character string

Example

// Add new column with column constraints
ALTER TABLE customer ADD COLUMN timeref CHAR(8);
  CHECK validtime(timeref);
  ERROR "Not a valid time string"
 
// Alter existing columns to add column constraints
ALTER TABLE customer;
  ALTER COLUMN available CALCULATED limit-balance;
  ALTER COLUMN limit RECALCULATE;
  ALTER COLUMN balance RECALCULATE
 
//or
ALTER TABLE customer;
  ALTER (available CALCULATED limit-balance,;
  limit RECALCULATE,;
  balance RECALCULATE)
 
// Add new column, add column constraint,
// modify column datatype and drop constraints then drop column
ALTER TABLE customer ADD (timeref CHAR(8))
 
ALTER TABLE customer;
  ALTER CONSTRAINT;
  (timeref SET CHECK validtime(timeref);
  ERROR "Not a valid time string")
 
ALTER TABLE customer;
  ALTER (timeref datetime);
  DROP CONSTRAINT (timeref CHECK, timeref ERROR)
 
ALTER TABLE customer DROP (timeref)
 
// Add an ONUPDATE table constraint 
ALTER TABLE customer;
  MODIFY ONUPDATE "check_update"
 
// Add and then remove CHECK table constraint
ALTER TABLE customer SET CHECK checkit() error "Invalid operation"
 
ALTER TABLE customer DROP CHECK
 
// Move position of column
CREATE TABLE temp;
 (field1 char(10), field3 dec(10,2), field2 date)
ALTER TABLE temp;
 MOVE (field3,3)
 
// Rename column
ALTER TABLE customer RENAME(first_name,forename)