FirebirdSQL logo
 SHADOWTABLE 

ALTER DOMAIN clauses

TO name

Renames the domain, as long as there are no dependencies on the domain, i.e. table columns, local variables or procedure arguments referencing it.

SET DEFAULT

Sets a new default value for the domain, replacing any existing default.

DROP DEFAULT

Deletes a previously specified default value and replace it with NULL.

SET NOT NULL

Adds a NOT NULL constraint to the domain;columns or parameters of this domain will be prevented from being written as NULL, i.e. a value is required.

Note

Adding a NOT NULL constraint to an existing domain will subject all columns using this domain to a full data validation, so ensure that the columns have no nulls before attempting the change.

DROP NOT NULL

Drops the NOT NULL constraint from the domain.

Note

An explicit NOT NULL constraint on a column that depends on a domain prevails over the domain.In this situation, the modification of the domain to make it nullable does not propagate to the column.

ADD CONSTRAINT CHECK

Adds a CHECK constraint to the domain.If the domain already has a CHECK constraint, it has to be deleted first, using an ALTER DOMAIN statement that includes a DROP CONSTRAINT clause.

TYPE

Changes the data type of the domain to a different, compatible one.The system will forbid any change to the type that could result in data loss.An example would be if the number of characters in the new type were smaller than in the existing type.

Important

When you alter the attributes of a domain, existing PSQL code may become invalid.For information on how to detect it, read the piece entitled The RDB$VALID_BLR Field in Appendix A.

What ALTER DOMAIN Cannot Alter

  • If the domain was declared as an array, it is not possible to change its type or its dimensions;nor can any other type be changed to an array type.

  • The collation cannot be changed without dropping the domain and recreating it with the desired attributes.

Who Can Alter a Domain

The ALTER DOMAIN statement can be executed by:

  • Administrators

  • The owner of the domain

  • Users with the ALTER ANY DOMAIN privilege

Domain alterations can be prevented by dependencies from objects to which the user does not have sufficient privileges.

ALTER DOMAIN Examples

  1. Changing the data type to INTEGER and setting or changing the default value to 2,000:

    ALTER DOMAIN CUSTNO
      TYPE INTEGER
      SET DEFAULT 2000;
  2. Renaming a domain.

    ALTER DOMAIN D_BOOLEAN TO D_BOOL;
  3. Deleting the default value and adding a constraint for the domain:

    ALTER DOMAIN D_DATE
      DROP DEFAULT
      ADD CONSTRAINT CHECK (VALUE >= date '01.01.2000');
  4. Changing the CHECK constraint:

    ALTER DOMAIN D_DATE
      DROP CONSTRAINT;
    
    ALTER DOMAIN D_DATE
      ADD CONSTRAINT CHECK
        (VALUE BETWEEN date '01.01.1900' AND date '31.12.2100');
  5. Changing the data type to increase the permitted number of characters:

    ALTER DOMAIN FIRSTNAME
      TYPE VARCHAR(50) CHARACTER SET UTF8;
  6. Adding a NOT NULL constraint:

    ALTER DOMAIN FIRSTNAME
      SET NOT NULL;
  7. Removing a NOT NULL constraint:

    ALTER DOMAIN FIRSTNAME
      DROP NOT NULL;