Background documentationDomains Locate this document in the navigation structure

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

When you create a table, you can define a domain for each column.

CREATE TABLE hotel.customer

(cno          FIXED(4) PRIMARY KEY CONSTRAINT cno_cons CHECK cno > 0,

 title       CHAR(7)  CONSTRAINT title_cons CHECK title IN ('Mr','Mrs','Company'),

 firstname   CHAR(20),

 name      CHAR(40) NOT NULL,

...)

The customer number has a maximum of four characters, the title 7, the first name 20 and the name 40 characters.

See also: SQL Reference Manual, CREATE TABLE Statement (create_table_statement)

You can predefine these domains in a domain definition.

Creating a Domain

To create a domain, use the CREATE DOMAIN statement.

CREATE DOMAIN name_domain CHAR(20)

Names are to be 20 characters long.

CREATE DOMAIN birthday_domain DATE DEFAULT DATE

CONSTRAINT birthday_domain > '1880-01-01' AND birthday_domain <= DATE

In the domain definition birthday_domain for the birthday, the current date is selected as the DEFAULT value. The birth date is not to be before 01/01/1880.

In a domain definition, you can specify a DEFAULT value and/or a CONSTRAINT definition.

  • The DEFAULT values must satisfy any existing restrictive conditions.

  • No constraint name can be specified in the CONSTRAINT definition and the domain is used as the column name.

Using the Domain

  1. Delete the person table, if necessary.

    DROP TABLE hotel.person

  2. Create the person table.

    CREATE TABLE hotel.person

    (pno FIXED(6) PRIMARY KEY,

     name name_domain,

     city CHAR(20))

  3. Add a birthday column with the domain birthday_domain.

    ALTER TABLE hotel.person ADD birthday birthday_domain

The person  table was created in accordance with the name_domain domain for names and with the birthday column with the domain definition birthday_domain.

See also: SQL Reference Manual, CREATE DOMAIN Statement (create_domain_statement)

Evaluating System Tables, COLUMNS, DOMAINS, DOMAINCONSTRAINTS

Deleting a Domain

To delete a domain, use the DROP DOMAIN statement.

DROP DOMAIN name_domain

You can use this SQL statement to delete the domain definition. Table definitions that used this domain definition are not deleted.

See also: SQL Reference Manual, DROP DOMAIN Statement (drop_domain_statement)

More Information

More examples for Data Definition