Firebird Documentation Index → Firebird 1.5 Language Ref. Update → DDL statements → ALTER TABLE
Firebird Home Firebird Home Prev: ALTER DOMAINFirebird Documentation IndexUp: DDL statementsNext: ALTER TRIGGER

ALTER TABLE

Table of Contents

ADD column: Context variables as defaults
ALTER COLUMN: POSITION now 1-based
FOREIGN KEY without target column references PK
UNIQUE constraints now allow NULLs
USING INDEX subclause

Tip

Find a more recent version at Firebird 5.0 Language Reference: 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 datatype 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: 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.

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 !

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.

USING INDEX subclause

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.

Prev: ALTER DOMAINFirebird Documentation IndexUp: DDL statementsNext: ALTER TRIGGER
Firebird Documentation Index → Firebird 1.5 Language Ref. Update → DDL statements → ALTER TABLE