| Firebird Documentation Index → Firebird 2.0 Language Ref. Update → DDL statements → ALTER TABLE |
|
|
|
NULL outcomeNULLsAvailable in: DSQL, ESQL
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
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 TABLEtablenameALTER [COLUMN]colnameDROP 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.
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 TABLEtablenameALTER [COLUMN]colnameSET DEFAULT<default><default>::=literal-value|context-variable| NULL
Example:
alter table Customers alter EnteredBy set default current_user
If you want to switch off a domain-based default on a column, set the column default to NULL.
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 TABLEtablenameALTER [COLUMN]colnamePOSITION<newpos><newpos>::= an integer between 1 and the number of columns
Example:
alter table Stock alter Quantity position 3
Don't confuse this with the POSITION in CREATE/ALTER TRIGGER. Trigger positions are and will remain 0-based.
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.
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 !
Changed in: 2.0
Description: In Firebird 2.0 and above, adding a foreign key constraint no longer requires exclusive access to the database.
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.
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] [CONSTRAINTconstraint-name]<constraint-type><constraint-definition>[USING [ASC[ENDING] | DESC[ENDING]] INDEXindex_name]
For a full discussion and examples, see CREATE TABLE :: USING INDEX subclause.
|
| Firebird Documentation Index → Firebird 2.0 Language Ref. Update → DDL statements → ALTER TABLE |