FirebirdSQL logo
 DOMAININDEX 

Examples Using ALTER TABLE

  1. Adding the CAPITAL column to the COUNTRY table.

    ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25);

  2. Adding the CAPITAL column with the NOT NULL and UNIQUE constraint and deleting the CURRENCY column.

    ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25) NOT NULL UNIQUE,
  DROP CURRENCY;

  3. Adding the CHK_SALARY check constraint and a foreign key to the JOB table.

    ALTER TABLE JOB
  ADD CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY),
  ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);

  4. Setting default value for the MODEL field, changing the type of the ITEMID column and renaming the MODELNAME column.

    ALTER TABLE STOCK
  ALTER COLUMN MODEL SET DEFAULT 1,
  ALTER COLUMN ITEMID TYPE BIGINT,
  ALTER COLUMN MODELNAME TO NAME;

  5. Restarting the sequence of an identity column.

    ALTER TABLE objects
  ALTER ID RESTART WITH 100;

  6. Changing the computed columns NEW_SALARY and SALARY_CHANGE.

    ALTER TABLE SALARY_HISTORY
  ALTER NEW_SALARY GENERATED ALWAYS AS
    (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
  ALTER SALARY_CHANGE COMPUTED BY
    (OLD_SALARY * PERCENT_CHANGE / 100);
See also

[fblangref50-ddl-tbl-create], [fblangref50-ddl-tbl-drop], [fblangref50-ddl-domn-create]

The ADD Clause

With the ADD clause you can add a new column or a new table constraint.The syntax for defining the column and the syntax of defining the table constraint correspond with those described for [fblangref50-ddl-tbl-create] statement.

Effect on Version Count

  • Each time a new column is added, the metadata change counter is increased by one

  • Adding a new table constraint does not increase the metadata change counter

Caution
Points to Be Aware of

  1. Adding a column with a NOT NULL constraint without a DEFAULT value will fail if the table has existing rows.When adding a non-nullable column, it is recommended either to set a default value for it, or to create it as nullable, update the column in existing rows with a non-null value, and then add a NOT NULL constraint.

  2. When a new CHECK constraint is added, existing data is not tested for compliance.Prior testing of existing data against the new CHECK expression is recommended.

  3. Although adding an identity column is supported, this will only succeed if the table is empty.Adding an identity column will fail if the table has one or more rows.