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:

  • Creates the specified schema.

  • Creates the domain in the specified schema.

  • Makes the user the owner of the new domain and grants all privileges on the new domain to the user.

  • Gives the user CREATE and ALTER privileges WITH GRANT OPTION on the new schema.

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.

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

Parameters

schema_name

Name of the schema in which to create the domain. The default is the current schema as defined by the USE command. If you specify schema_name and the schema does not exist, NuoDB creates it. Domains cannot be created in the SYSTEM schema.

domain_name

Name (optionally schema-qualified) of the domain to create.

data_type

Underlying data type of the domain. See SQL Data Types for data types supported.

NULL | NOT NULL

Whether or not null values are allowed. The default is NULL

DEFAULT default_value

The DEFAULT clause specifies a default value for columns of the domain data type. The value can be any variable-free expression but sub-selects are not allowed. NuoDB uses the default expression in any insert operation that does not specify a value for the column. If there is no default value defined for a domain then the default value is NULL.

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