CREATE DOMAIN

CREATE DOMAIN — create 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.
To create the domain, you must have CREATE privileges for the schema in which the domain is created.
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)A set of peers., 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.

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