Why do we need to create Domains, Defaults & Check Constraints?
When you create domains, defaults and attach them to the columns in a data model, you are actually speeding up the data model creation process by using the predefined datatypes for all the similar columns. Metadata/data in the database and data structures will be consistent across the data models so that conflicts can be reduced. Check constraints enforce some rules on columns in the data model as per your instructions.
In this section, you will see how you can create domain, defaults and check constraints by using TOAD Data Modeler for the sample data shown below. To know more about the definitions and meaning of a domain, defaults and check constraints, please refer our earlier section Data Modeling Objects listed under the category Data Modeling.
Sample Data:
| Column Name | Data Type |
|---|---|
| FULL_NAME | NOT NULL |
| STREET_NAME | VARCHAR2(30) NOT NULL |
| CITY_NAME | VARCHAR2(30) NOT NULL |
| GENDER | CHAR(1) NOT NUL |
| SSN | CHAR(9) NOT NULL |
| RECORD_DATE | DATE NOT NULL |
| USER_NAME | VARCHAR2(30) NOT NULL |
Sample Data Observation:
- You see columns FULL_NAME, CITY_NAME, STREET_NAME, and USER_NAME with the same datatype of VARCHAR2 (30). So, first create a domain with datatype as VARCHAR2 (30). Then attach it to four of these columns.
- For column GENDER, it can take only two values. Create a check constraint.
- For column SSN, it should contain only numerical values. So create check constraint.
- For column RECORD_DATE, it will store system date. So create a default.
- For column USER_NAME, it will store the name of the user who inserted or updated that record. So create a default.
Steps to be followed in Toad:
- Go to TOAD DATA MODELER and create a table “DOMAIN_RULE_DEFAULT” as mentioned in the section Create Data Modeling Objects
- Domain:
- Creating Domain: Go to menu “MODEL/DOMAIN”. In the new window, click add. By default, a domain is created by TOAD. Change caption and name to “NAME_DOMAIN” and change datatype to VARCHAR2(30).
- Attaching Domains: You have to attach domain “NAME_DOMAIN” for FULL_NAME, CITY_NAME, STREET_NAME, and USER_NAME. While creating columns, you can see a list box for attaching DOMAINS. Select the domain “NAME_DOMAIN” and attach it to those columns.
- Default:
- Creating Default for column RECORD_DATE: Go to menu “MODEL/DEFAULT”. In the new window, click add. By default a domain created by TOAD. Edit in General tab and Change caption and name to “TODAY_DATE_DEFAULT”. In SQL tab, type the syntax “SYSDATE”.
- Creating Default for column USER_NAME: For column USER_NAME, create another default RECORD_BY_USER_DEFAULT and in SQL tab, type the syntax “USER”.
- Attaching columns to Defaults: You have to attach the Default “TODAY_DATE_DEFAULT” to column “RECORD_DATE”. While creating columns, you will see a list box for attaching Defaults. Select the default “TODAY_DATE_DEFAULT” and attach it to RECORD_DATE column. Do the same procedure for the other column USER_NAME to attach it to the default “RECORD_BY_USER_DEFAULT”.
- CHECK Constraint:
- Creating and attaching Check Constraint: While creating the column gender, you can see a tab “check constraints”. Click add. By default a check constraint is created by TOAD. Edit in General tab and change caption and name to “GENDER_CHECK”. In sql tab, type “gender in (‘M’, ‘F’)”.
- Similarly do it for SSN column also. In sql tab, type SSN BETWEEN ‘000000001’ AND ‘999999999’.
This is how the table looks in a Data Model.
| Column Name | Data Type |
|---|---|
| FULL_NAME | NAME_DOMAIN NN |
| STREET_NAME | NAME_DOMAIN NN |
| CITY_NAME | NAME_DOMAIN NN |
| GENDER | Char(1) NN |
| SSN | Char(9) NN |
| RECORD_DATE | Date NN |
| USER_NAME | NAME_DOMAIN NN |
Note: NN means NOT NULL.
In few columns, you see NAME_DOMAIN. The domain that you attached is displayed here instead of data type.
DDL Script for this table:
CREATE TABLE “DOMAIN_RULE_DEFAULT”(
“FULL_NAME” Varchar2(30 ) NOT NULL,
“STREET_NAME” Varchar2(30 ) NOT NULL,
“CITY_NAME” Varchar2(30 ) NOT NULL,
“GENDER” Char(1 ) NOT NULL CONSTRAINT “GENDER_CHECK” CHECK (GENDER IN (‘M’, ‘F’)),
“SSN” Char(9 ) NOT NULL CONSTRAINT “SSN_CHECK” CHECK (SSN BETWEEN (‘000000001’ AND ‘999999999’)),
“RECORD_DATE” Date DEFAULT Sysdate NOT NULL,
“USER_NAME” Varchar2(30 ) DEFAULT USER NOT NULL)