<< CREATE TABLE | FB 2.5 Language Reference | RECREATE TABLE >>

ALTER TABLE

Available in: DSQL, ESQL

ADD column: Context variables as defaults

Changed in: IB

Description:

Any context variable that is assignment-compatible to the new column's data type can be used as a default. This was already the case in InterBase 6, but the Language Reference only mentioned USER.

Example

 alter table MyData
    add MyDay date default current_date

ALTER COLUMN also for generated (computed) columns

Available in: DSQL

Added in: 2.5

Description

Firebird 2.5 supports the altering of generated (computed) columns, something that was previously impossible. Only the data type and the generation expression can be changed; you cannot change a base column into a generated column or vice versa.

Syntax

 ALTER TABLE tablename ALTER [COLUMN] gencolname
    [TYPE datatype]
    {GENERATED ALWAYS AS | COMPUTED BY} (expression)

Example

 create table nums (a int, b generated always as (3*a));
 commit;

 alter table nums alter b generated always as (4*a + 7);
 commit; 

Notice that you can use GENERATED ALWAYS AS when altering columns defined with COMPUTED BY and vice versa.

ALTER COLUMN ... TYPE no longer fails if column is used in trigger or SP

Changed in: 2.5

Description

Previously, if a table column was referenced in a stored procedure or trigger, the column's type could not be changed, even if the change would not break the PSQL code. Now such changes are permitted – even if they do break the code.

Warning: This means that, in the current situation, you can commit changes that break SP's or triggers without getting as much as a warning! For information on how to track down invalidated PSQL modules after a column type change, please read the note The RDB$VALID_BLR field, near the end of this document.

ALTER COLUMN: DROP DEFAULT

Available in: DSQL

Added in: 2.0

Description

Firebird 2 adds the possibility to drop a column-level default. Once the default is dropped, there will either be no default in place or – if the column's type is a DOMAIN with a default – the domain default will resurface.

Syntax

 ALTER TABLE tablename ALTER [COLUMN] colname DROP DEFAULT

Example

 alter table Trees alter Girth drop default

An error is raised if you use DROP DEFAULT on a column that doesn't have a default or whose effective default is domain-based.

back to top of page

ALTER COLUMN: SET DEFAULT

Available in: DSQL

Added in: 2.0

Description

Firebird 2 adds the possibility to set/alter defaults on existing columns. If the column already had a default, the new default will replace it. Column-level defaults always override domain-level defaults.

Syntax

 ALTER TABLE tablename ALTER [COLUMN] colname SET DEFAULT <default>

 <default> ::= literal-value | context-variable | NULL

Example

 alter table Customers alter EnteredBy set default current_user

Tip: If you want to switch off a domain-based default on a column, set the column default to NULL.

back to top of page

ALTER COLUMN: POSITION now 1-based

Changed in: 1.0

Description

When changing a column's position, the engine now interprets the new position as 1-based. This is in accordance with the SQL standard and the InterBase documentation, but in practice InterBase interpreted the position as 0-based.

Syntax

 ALTER TABLE tablename ALTER [COLUMN] colname POSITION <newpos>

 <newpos> ::= an integer between 1 and the number of columns

Example

 alter table Stock alter Quantity position 3

Note: Don't confuse this with the POSITION in CREATE/ALTER TRIGGER. Trigger positions are and will remain 0-based.

CHECK accepts NULL outcome

Changed in: 2.0

Description

If a CHECK constraint resolves to NULL, Firebird versions before 2.0 reject the input. Following the SQL standard to the letter, Firebird 2.0 and above let NULLs pass and only consider the check failed if the outcome is false. For more information see under CREATE TABLE.

back to top of page

FOREIGN KEY without target column references PK

Changed in: IB

Description

If you create a foreign key without specifying a target column, it will reference the primary key of the target table. This was already the case in InterBase 6, but the IB Language Reference wrongly states that in such cases, the engine scans the target table for a column with the same name as the referencing column.

Example

 create table eik (
   a int not null primary key,
   b int not null unique
 );

 create table beuk (
   b int
 );

 alter table beuk
   add constraint fk_beuk
   foreign key (b) references eik;

 -- beuk.b now references eik.a, not eik.b !

FOREIGN KEY creation no longer requires exclusive access

Changed in: 2.0

Description

In Firebird 2.0 and above, adding a foreign key constraint no longer requires exclusive access to the database.

back to top of page

GENERATED ALWAYS AS

Added in: 2.1

Description

Instead of COMPUTED [BY], you may also use the SQL-2003-compliant equivalent GENERATED ALWAYS AS for computed fields.

Syntax

 colname [coltype] GENERATED ALWAYS AS (expression)

Example

 alter table Friends
   add fullname varchar(74)
   generated always as
     (firstname || coalesce(' ' || middlename, '') || ' ' || lastname)

UNIQUE constraints now allow NULLs

Changed in: 1.5

Description

In compliance with the SQL-99 standard, NULLs – even multiple – are now allowed in columns with a UNIQUE constraint. For a full discussion, see CREATE TABLE :: UNIQUE constraints now allow NULLs.

back to top of page

USING INDEX subclause

Available in: DSQL

Added in: 1.5

Description

A USING INDEX subclause can be placed at the end of a primary, unique or foreign key definition. Its purpose is to:

• provide a user-defined name for the automatically created index that enforces the constraint, and
• optionally define the index to be ascending or descending (the default being ascending).

Syntax

 [ADD] [CONSTRAINT constraint-name]
    <constraint-type> <constraint-definition>
    [USING [ASC[ENDING] | DESC[ENDING]] INDEX index_name]

For a full discussion and examples, see CREATE TABLE :: USING INDEX subclause.

See also:
ALTER DOMAIN
CREATE DOMAIN
CREATE TABLE
DDL - Data Definition Language
SQL2003 compliant alternative for computed fields

back to top of page
<< CREATE TABLE | FB 2.5 Language Reference | RECREATE TABLE >>