EnterpriseDB: Domains Oracle Tips by Burleson Consulting

A domain is a data type that has constraints applied to it.

CREATE DOMAIN

CREATE DOMAIN name AS data_type
            [ DEFAULT <default> ]
            [ CONSTRAINT {NOT NULL|NULL|CHECK (<expression>)}]

A domain can be a data item such as sex.  The definition of sex might be CHAR(1) and it may be constrained to values  of 'M', 'F', or 'U' (for unknown).  We can set a default of 'U' and force it to not accept NULL values.  To create this domain, we would use:

CREATE DOMAIN sex AS CHAR
  CONSTRAINT sex_cons
  DEFAULT 'U'
  NOT NULL
  CHECK (VALUE IN ('M', 'F', 'U'));

Default, check and NULL/NOT NULL are optional and the domain will default to nullable.  The CONSTRAINT name is required if you are defining a DEFAULT, NULL/NOT NULL or CHECK.

You must use the keyword VALUE when you reference the column instead of using the actual column name. VALUE is the equivalent of SELF in OO programming.

You can include a SCHEMA name in your declaration if you want the domain created in a particular schema.

CREATE DOMAIN hr.emp_ssn AS VARCHAR2(9)
  CONSTRAINT hr_emp_ssn
  CHECK( LENGTH(VALUE)  =  9);

You can then use your domains in your create table commands:

CREATE TABLE emp_stuff (
  empno NUMBER(5),
  employee_sex sex;
  employee_ssn hr.emp_ssn );

ALTER DOMAIN

You can alter a domain to change a default or a NULL/NOT NULL.

ALTER DOMAIN sex SET DEFAULT 'M';
ALTER DOMAIN sex SET NULL;

You can also drop the default and the NULL/NOT NULL.

ALTER DOMAIN sex DROP DEFAULT;
ALTER DOMAIN sex DROP NOT NULL;

You can add a new constraint:

ALTER DOMAIN sex ADD
  CONSTRAINT upper_sex_cons
    CHECK (UPPER(VALUE) = VALUE);

Or drop a constraint.

ALTER DOMAIN sex DROP CONSTRAINT upper_sex_cons;

You can change the schema where it resides.

ALTER DOMAIN sex SET SCHEMA hr;

GRANT DOMAIN

You don't need any special grants for a domain.

REVOKE DOMAIN

There is nothing to revoke from a domain.

DROP DOMAIN

You can drop a domain with the DROP DOMAIN command.

DROP DOMAIN sex;

If the DOMAIN is being used anywhere in the database, you must use the CASCADE option to also remove the domain from those objects.

DROP DOMAIN sex CASCADE;

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.