Understanding Domains

A domain is the set of all values that are legal for a particular column in a table. Suppose, for example, that your EMPLOYEE table had a DEPENDANT field that your company policy states must be an INTEGER between 0 and 14. The domain of DEPENDANT would then be 0,1,2,3,4,5,6,7,8,9,10,12,13,14. Or, suppose you were maintaining a table for a tablecloth inventory that has a COLOR column, and all of your tablecloths were white, beige, or blue. The domain of the COLOR column would then be WHITE, BEIGE, BLUE

Using the CREATE DOMAIN Statement to Create Domains"), you can use the domain as a data type when defining a column. Suppose, for example, that you had a CUSTOMER table with a STATE field. You could define the domain of the STATE field by creating a STATE_CODE domain using this SQL statement:

CREATE DOMAIN STATE_CODE AS CHAR(2)
CONSTRAINT VALID_STATE_ABBREVIATION
CHECK (VALUE IN ('AL', 'AK', 'AZ', 'CO', 'CT', ... ))

Note You would list the remaining 45 state codes in place of the "..." in the VALUE IN section of the CREATE DOMAIN statement.


To have the DBMS validate data as it is entered into the STATE field of the CUSTOMER table, use the STATE_CODE domain as the data type for the state field when creating the table, as shown in this SQL statement:

CREATE TABLE CUSTOMER
(NAME VARCHAR(25),
ADDRESS VARCHAR(35),
CITY VARCHAR(20),
STATE STATE_CODE,
ZIP CODE INTEGER)

The beauty of defining a domain is that you can change it on the fly without having to alter the structure of the table or recompile any existing stored procedures or application programs.

Suppose, for example, that Puerto Rico were to become a state; you could use the ALTER DOMAIN statement to add PR to the list of valid state abbreviations. The DBMS would then automatically allow the user to enter PR for the STATE field, since the updated STATE_CODE domain (stored in the system tables) would include PR as a valid state code the next time the DBMS referred to it in checking the value in the STATE field of a row to be added to the CUSTOMER table.


Comments

Author: rohit02 Dec 2008 Member Level: Bronze   Points : 0

its excellent frnd the way u explained is too good
keep it up

Author: ChandraShekar Thota03 Jan 2009 Member Level: Gold   Points : 0

Nice contribution from u

Chandrashekar Thota(Editor, MVP)




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: