ALTER TABLE

Home

4D v16

ALTER TABLE



	ALTER TABLE sql_name {ADD [TRAILING] column_definition [PRIMARY KEY] \| DROP sql_name \| ADD primary_key_definition \| DROP PRIMARY KEY \| ADD foreign_key_definition \| DROP CONSTRAINT sql_name \| [{ENABLE \| DISABLE} REPLICATE] \| [{ENABLE \| DISABLE} LOG] \| [MODIFY sql_name {ENABLE \| DISABLE} AUTO_INCREMENT] \| [MODIFY sql_name {ENABLE \| DISABLE} AUTO_GENERATE] \| [{ENABLE \| DISABLE} TRIGGERS] \| SET SCHEMA sql_name}

Description

The ALTER TABLE command is used to modify an existing table (sql_name). You can carry out one of the following actions:
Passing ADD column_definition adds a column to the table. The TRAILING keyword (which must be placed in front of column_definition if it is used) forces the column to be created after the last existing column of the table in the structure file. This option is useful when columns containing data have been deleted from the table (without the data being erased), to prevent existing data from being reassigned to the new column.

Note: This command does not allow a field (column) of the Object type to be added.

The PRIMARY KEY keyword is used to set the primary key when a column is added.
Passing DROP sql_name removes the column named sql_name from the table.
Passing ADD primary_key_definition adds a PRIMARY KEY to the table.
Passing DROP PRIMARY KEY removes the PRIMARY KEY of the table.
Passing ADD foreign_key_definition adds a FOREIGN KEY to the table.
Passing DROP CONSTRAINT sql_name removes the specified constraint from the table.
Passing ENABLE REPLICATE or DISABLE REPLICATE enables or disables the mechanism allowing replication of the table (see the Replication via SQL section).
Passing ENABLE LOG or DISABLE LOG enables or disables journaling for the table.
Passing ENABLE AUTO_INCREMENT or DISABLE AUTO_INCREMENT enables or disables the "Autoincrement" option for Longint type fields. Passing ENABLE AUTO_GENERATE or DISABLE AUTO_GENERATE enables or disables the "Auto UUID" option for Alpha fields of the UUID type. In both these cases, you must first pass the MODIFY keyword followed by the sql_name of the column to modify.
Passing ENABLE TRIGGERS or DISABLE TRIGGERS enables or disables triggers for the table. If you want to manage triggers globally at the database level, you need to use ALTER DATABASE.
Passing SET SCHEMA sql_name transfers the table to the sql_name schema.

The command returns an error:

when the optional ENABLE LOG parameter is passed and no valid primary key is defined,
if you attempt to modify or delete the definition of the table’s primary key without disabling journaling by means of DISABLE LOG.

Example 1

This example creates a table, inserts a set of values into it, then adds a Phone_Number column, adds another set of values and then removes the ID column:

CREATE TABLE ACTOR_FANS
(ID INT32, Name VARCHAR);

INSERT INTO ACTOR_FANS
(ID, Name)
VALUES(1, 'Francis');

ALTER TABLE ACTOR_FANS
ADD Phone_Number VARCHAR;

INSERT INTO ACTOR_FANS
(ID, Name, Phone_Number)
VALUES (2, 'Florence', '01446677888');

ALTER TABLE ACTOR_FANS
DROP ID;

Example 2

Example for activating the "Autoincrement" option of the Longint type [Table_1]id field:

 Begin SQL

    ALTER TABLE Table_1 MODIFY id ENABLE AUTO_INCREMENT;

 End SQL

Deactivating the option:

 Begin SQL

    ALTER TABLE Table_1 MODIFY id DISABLE AUTO_INCREMENT;

 End SQL

Example for activating the "Auto UUID" of the Alpha type [Table_1]uid field:

 Begin SQL

    ALTER TABLE Table_1 MODIFY uid ENABLE AUTO_GENERATE;

 End SQL

Deactivating the option:

 Begin SQL

    ALTER TABLE Table_1 MODIFY uid DISABLE AUTO_GENERATE;

 End SQL