CREATE DOMAIN

CREATE DOMAIN — define a new SQL domain

Syntax

CREATE DOMAIN [schema_name.]domain_name
    [AS] data_type    
    [ [NOT] NULL ]
    [ DEFAULT default_value ]  

Description

Use CREATE DOMAIN to register a new data type domain for use in the current database. The user who creates a SQL domain becomes its owner.
If the CREATE DOMAIN statement specifies the name of an existing schema then the user must have CREATE privileges for the specified schema. If the user has CREATE privileges then NuoDB creates the domain in the specified schema. If the user does not have CREATE privileges on the specified schema then the operation fails. Use the GRANT CREATE ON SCHEMA statement to give a user CREATE privileges on a schema (See GRANT).
If the CREATE DOMAIN statement specifies the name of a schema that does not yet exist, then NuoDB does the following:

The name of the domain you create must be unique among the domains already in the specified schema.
Domains are useful for specifying, as an abstraction, attributes to maintain consistency of similar column definitions across tables in a database. For example, several tables might contain a unique identifier ID column, all requiring the same data type, a NOT NULL constraint and the same default value. This can be accomplished efficiently by defining a domain rather than setting up each table's unique identifier ID column individually.

Note: A NuoDB SQL domain is distinct from a NuoDB domain (NuoDB), which is a set of peers.

Parameters

Note: If a default value is specified for a particular column then it overrides any default associated with the domain. In turn, the domain default overrides any default value associated with the underlying data type.

Example

Create domains and use the domains in a table definition.

CREATE DOMAIN id_type      INTEGER  NOT NULL;
CREATE DOMAIN zipcode_type CHAR(05) NOT NULL;
CREATE DOMAIN product_type string   DEFAULT  'DataBase Software';
 
CREATE TABLE company (id             id_type  GENERATED ALWAYS AS IDENTITY,
                      company_name   STRING   NOT NULL,
                      street_address STRING,
                      city           STRING  NOT NULL,
                      zip_code       zipcode_type,
                      product        product_type);
 
INSERT INTO company (company_name, street_address, city, zip_code)
            VALUES ('NuoDB,Inc.', 'First Street', 'Cambridge', '02142');
INSERT INTO COMPANY (company_name, street_address, city, zip_code, product)
            VALUES ('NuoDB Corporate','First Street','Cambridge','02142','Corporate');
 
SELECT * FROM COMPANY;
 ID   COMPANY_NAME   STREET_ADDRESS    CITY    ZIP_CODE       PRODUCT     
 --- --------------- --------------- --------- --------- -----------------
  1  NuoDB,Inc.       First Street   Cambridge   02142   DataBase Software
  2  NuoDB Corporate  First Street   Cambridge   02142   Corporate