Using a Domain in a Column Definition - Firebird

Example

In a certain database, SYSUSER is a domain of up to 31 characters, having a DEFAULT value that is to be obtained by reading the context variable CURRENT_USER:

CREATE DOMAIN SYSUSER AS VARCHAR(31) DEFAULT CURRENT_USER;

A table is defined, having a column UPDATED_BY that uses this SYSUSER domain:

CREATE TABLE LOANS ( LOAN_DATE DATE, UPDATED_BY SYSUSER, LOAN_FEE DECIMAL(15,2));

A client submits an INSERT statement for the LOANS table:

INSERT INTO ORDERS (LOAN_DATE, LOAN_FEE) VALUES ('16-MAY-2004', 10.75);

Because the statement does not name the UPDATED_BY column in its column list, Firebird automatically inserts the user name of the current user, ALICEFBIRD:

SELECT * FROM LOANS;

returns

16-MAY-2004 ALICEFBIRD 10.75

Domain Overrides

Columns defined using a domain can override some attributes inherited from the domain, by replacing an inherited attribute with an equivalent attribute clause. The column definition can also be extended by adding further attributes. In Table, you can see which attributes can and cannot be overridden.

Domain Attributes and Column Overrides

Domain Attributes and Column Overrides

The following statement shows how to extend the attributes of a column that is being defined to use a domain, using an earlier domain definition example:

CREATE DOMAIN TEL_NUMBER AS VARCHAR(18) CHECK (VALUE LIKE '(0%)%');

Let’s say we want to define a table having a telecom number column in it. We want the domain’s attributes, but we also want to ensure that any non-numeral characters are input in uppercase:

CREATE TABLE LIBRARY_USER ( USER_ID INTEGER NOT NULL. ... <other columns>, PHONE_NO TEL_NUMBER, CONSTRAINT CHK_TELNUM_UPPER CHECK (PHONE_NO = UPPER(PHONE_NO)) );

Now, we have an extra CHECK validation on this particular column. This statement:

INSERT INTO LIBRARY_USER VALUES(USER_ID, PHONE_NO) VALUES (99, '(09) 43889 wish');

fails, because the extra CHECK constraint requires the phone number to be '(09) 43889
WISH'.

Where Domains Won’t Work

A domain cannot be used

  • With the CAST (aValue AS <another type>) function.
  • In lieu of a data type when defining input or output arguments for a stored procedure.
  • To declare the type of a variable in a trigger or stored procedure.
  • To define the data type of the elements of an ARRAY. A domain can itself be an ARRAY type, however.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics