Oracle AI Database 26ai - SQL Domain
SQL domain can include a set of constraints and attributes, and by assigning it to a column, we can apply those constraints to that column. In other words, SQL Domain enables the extension of data types in a way that aligns with business requirements.
One of the most important use cases of this feature arises when we want to apply specific conditions to the input values of a column. For example, for an Age column of type NUMBER, we may want to prevent values less than 18 by enforcing the condition Age >= 18. Or, in a more practical example, for a column intended to store email addresses, we can apply a rule so that the column only accepts input in the format text@text.text.
Of course, in versions prior to 26ai, this could be achieved using different approaches such as triggers, check constraints, and so on. For instance, using a check constraint, we can enforce that values inserted into the Email column must match the text@text.text format:
SQL> CREATE TABLE EMAIL_ADDRESS
2 (
3 id NUMBER(10),
4 person_id NUMBER(20),
5 email VARCHAR2(500) constraint check_email
6 CHECK (regexp_like (email, '^(\S+)\@(\S+)\.(\S+)$'))
7 );
Table createdSQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail');
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail.com');
1 row inserted
SQL> update EMAIL_ADDRESS set email='vahidusefzadeh';
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'In a schema, there may be multiple tables that store email addresses. In such cases, the check constraint must be repeated for every column. Now imagine we want to change the email format or disable all such constraints — clearly, managing check constraints in these scenarios becomes challenging.
SQL Domain is highly flexible in this regard and offers multiple capabilities. Once created, it can be reused across multiple tables. Each domain must include at least one data type:
SQL> create domain DMN_check_email as varchar2(500);
Domain created.A SQL domain can include NOT NULL, NULL, or check constraints. In the following example, we create a domain with a VARCHAR2 data type and a check constraint:
SQL> create domain DMN_check_email as varchar2(500)
constraint check_email check (regexp_like (DMN_check_email, '^(\S+)\@(\S+)\.(\S+)$'));
Domain created.In this example, we explicitly named the constraint check_email. Naming constraints is optional; if not specified, Oracle automatically assigns a name.
To control how data is displayed, we can also use the DISPLAY clause:
SQL> create domain DMN_check_email as varchar2(500)
constraint check_email check (regexp_like (DMN_check_email, '^(\S+)\@(\S+)\.(\S+)$'))
display upper('Email: '||DMN_check_email);
Domain created.After creating DMN_check_email, we can assign it to a table column. A domain can be used by multiple tables:
SQL> CREATE TABLE EMAIL_ADDRESS
2 (
3 id NUMBER(10),
4 person_id NUMBER(20),
5 email DMN_check_email
6 );
Table createdSQL> desc EMAIL_ADDRESS
Name Null? Type
---------------------- -------- ----------------------------
ID NUMBER(10)
PERSON_ID NUMBER(20)
EMAIL VARCHAR2(500) USEF.DMN_CHECK_EMAILThis can also be done in other ways:
SQL> CREATE TABLE EMAIL_ADDRESS
2 (
3 id NUMBER(10),
4 person_id NUMBER(20),
5 email varchar2(500) domain DMN_check_email
6 );
Table createdSQL> CREATE TABLE EMAIL_ADDRESS
2 (
3 id NUMBER(10),
4 person_id NUMBER(20),
5 email domain DMN_check_email
6 );
Table createdTesting the domain behavior:
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail');
ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail.com');
1 row inserted
SQL> insert into EMAIL_ADDRESS values(2,46,'vahidusefzadeh@yahoo.com');
1 row created.
SQL> update EMAIL_ADDRESS set email='vahidusefzadeh';
'ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated'SQL> select * from EMAIL_ADDRESS;
ID PERSON_ID EMAIL
---------- ---------- ------------------------------
1 34 vahidusefzadeh@gmail.com
2 46 vahidusefzadeh@yahoo.comTo apply display formatting, we can use the DOMAIN_DISPLAY function:
SQL> select id,PERSON_ID,DOMAIN_DISPLAY(EMAIL) EMAIL from EMAIL_ADDRESS;
ID PERSON_ID EMAIL
---------- ---------- ----------------------------------------
1 34 EMAIL: VAHIDUSEFZADEH@GMAIL.COM
1 46 EMAIL: VAHIDUSEFZADEH@YAHOO.COMDomains can also be applied to existing tables:
SQL> CREATE TABLE EMAIL_ADDRESS2
2 (
3 id NUMBER(10),
4 person_id NUMBER(20),
5 email varchar2(1000)
6 );
Table createdSQL> insert into EMAIL_ADDRESS2 values(1,34,'vahidusefzadeh');
1 row inserted
SQL> insert into EMAIL_ADDRESS2 values(2,34,'vahidusefzadeh@gmail.com');
1 row inserted
SQL> commit;
Commit complete
SQL> alter table EMAIL_ADDRESS2 modify email domain DMN_CHECK_EMAIL;
'ORA-02293: cannot validate (USEF.) - check constraint violated'
SQL> delete EMAIL_ADDRESS2 where id=1;
1 row deleted
SQL> commit;
Commit complete
SQL> alter table EMAIL_ADDRESS2 modify email domain DMN_CHECK_EMAIL;
Table alteredIf existing data violates the domain constraint, Oracle raises an error until invalid rows are corrected.
To view created domains:
SQL> select owner, name from user_domains;
OWNER NAME
--------------- ---------------
USEF DMN_CHECK_EMAILDomain constraints can be viewed using:
select * from user_domain_constraints where domain_name='DMN_CHECK_EMAIL';
NAME SEARCH_CONDITION STATUS
----------- ------------------------------------------------------- --------
CHECK_EMAIL regexp_like (DMN_check_email, '^(\S+)\@(\S+)\.(\S+)$') ENABLEDThe DBA_DOMAIN_COLS view is also very useful in this context.
Built-in Oracle Domains
Oracle automatically provides several predefined domains, which can be viewed via ALL_DOMAINS:
SQL> select name from all_domains where owner='SYS';
PHONE_NUMBER_D
EMAIL_D
DAY_SHORT_D
DAY_D
MONTH_SHORT_D
MONTH_D
YEAR_D
POSITIVE_NUMBER_D
NEGATIVE_NUMBER_D
NON_POSITIVE_NUMBER_D
NON_NEGATIVE_NUMBER_D
MAC_ADDRESS_D
SSN_D
CREDIT_CARD_NUMBER_D
IPV4_ADDRESS_D
IPV6_ADDRESS_D
SUBNET_MASK_D
SHA1_D
SHA256_D
SHA512_D
CIDR_D
MIME_TYPE_D
22 rows selected.Examples include EMAIL_D, IPV4_ADDRESS_D, CREDIT_CARD_NUMBER_D, and more.
Example using IPV4_ADDRESS_D:
SQL> create table tbl1(ip_v4 IPV4_ADDRESS_D);
Table created
SQL> insert into tbl1 values('10.22.44.66');
1 row inserted
SQL> insert into tbl1 values('10.22.44.666');
ORA-11534: check constraint (USEF.SYS_C008400) due to domain constraint SYS.SYS_DOMAIN_C0015 of domain SYS.IPV4_ADDRESS_D violated
SQL> insert into tbl1 values('10.22.44.1.5');
ORA-11534: check constraint (USEF.SYS_C008400) due to domain constraint SYS.SYS_DOMAIN_C0015 of domain SYS.IPV4_ADDRESS_D violatedTo extract the constraint definition of a domain, we can use DBMS_METADATA:
select dbms_metadata.get_ddl('SQL_DOMAIN', 'IPV4_ADDRESS_D','SYS') domain_ddl from dual;CREATE DOMAIN "SYS"."IPV4_ADDRESS_D" AS VARCHAR2(15) CHECK (REGEXP_LIKE(ipv4_address_d,'^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$')) ENABLEDropping a Domain
If a domain has not been assigned to any column, it can be dropped easily:
SQL> drop domain DMN_check_email;
DoneIf the domain is in use, Oracle raises an error. Using FORCE removes the domain and also removes the associated constraints from columns:
SQL> drop domain DMN_check_email;
'ORA-11502: Message 11502 not found; product=RDBMS; facility=ORA'Using FORCE PRESERVE removes the domain but keeps the constraint in place:
SQL> DESC EMAIL_ADDRESS
Name Type
----------- --------------------------------------
ID NUMBER(10)
PERSON_ID NUMBER(20)
EMAIL VARCHAR2(500) USEF.DMN_CHECK_EMAIL SQL> drop domain DMN_check_email force;
DoneSQL> DESC EMAIL_ADDRESS
Name Type
------------------------ ---------------
ID NUMBER(10)
PERSON_ID NUMBER(20)
EMAIL VARCHAR2(500)SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
1 row insertedUsing FORCE PRESERVE removes the domain but keeps the constraint in place:
SQL> drop domain DMN_check_email force PRESERVE;
Domain dropped.
SQL> DESC EMAIL_ADDRESS
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(10)
PERSON_ID NUMBER(20)
EMAIL VARCHAR2(500)
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
ORA-02290: check constraint (USEF.SYS_C008407) violated
Multi-Column Domain
All examples so far were Single Column Domains. Oracle also supports Multi-Column Domains and Flexible Domains.
Example of a Multi-Column Domain:
SQL> CREATE DOMAIN IR_NORTH AS
(
province AS VARCHAR2(100),
zipcode AS NUMBER
)
CONSTRAINT IR_NORTH_ch CHECK(province in ('Mazandaran','Golestan','GILAN') and zipcode >1234);
Domain created.SQL> CREATE TABLE TBL_IR_NORTH(
id number(10),
province VARCHAR2(100),
zipcode NUMBER,
domain IR_NORTH(province,zipcode)
);
Table created.SQL> insert into TBL_IR_NORTH values(1,'Mazandaran',12345);
1 row created.
SQL> insert into TBL_IR_NORTH values(2,'Tehran',123456);
ERROR at line 1:
ORA-11534: check constraint (SYS.SYS_C008299) due to domain constraint SYS.IR_NORTH_CH of domain SYS.IR_NORTH violated
SQL> insert into TBL_IR_NORTH values(2,'Mazandaran',1);
ERROR at line 1:
ORA-11534: check constraint (SYS.SYS_C008299) due to domain constraint SYS.IR_NORTH_CH of domain SYS.IR_NORTH violatedFlexible Domain
To create a Flexible Domain, at least two domains are required:
SQL> CREATE DOMAIN IR_NORTH AS
(
province AS VARCHAR2(100),
zipcode AS NUMBER
)
CONSTRAINT IR_NORTH_ch CHECK(province in ('Mazandaran','Golestan','GILAN') and zipcode >1234);
Domain created.SQL> CREATE DOMAIN IR_SOUTH AS
(
province AS VARCHAR2(100),
zipcode AS NUMBER
)
CONSTRAINT IR_SOUTH_CH CHECK(province in ('Khozestan','Bushehr') and zipcode between 1 and 1000);
Domain created.SQL> create flexible domain IR_Regions (province,zipcode)
choose domain using (Regions varchar2(10))
from case
when Regions in ('SOUTH') then IR_SOUTH(province,zipcode)
when Regions in ('NORTH') then IR_NORTH(province,zipcode)
end;
Domain created.SQL> CREATE TABLE TBL_IR_Regions(
id number(10),
province VARCHAR2(100),
zipcode NUMBER,
Regions varchar2(10),
domain IR_Regions(province,zipcode)using (Regions)
);
Table created.SQL> insert into TBL_IR_Regions values(1,'Khozestan',10,'SOUTH');
1 row created.
SQL> insert into TBL_IR_Regions values(2,'Khozestan',3000145,'SOUTH');
ORA-11534: check constraint (SYS.SYS_C008303) due to domain constraint SYS.SYS_DOMAIN_C0041 of domain SYS.IR_REGIONS violated
SQL> insert into TBL_IR_Regions values(2,'Khozestan',30,'NORTH');
ORA-11534: check constraint (SYS.SYS_C008304) due to domain constraint SYS.SYS_DOMAIN_C0040 of domain SYS.IR_REGIONS violated
SQL> insert into TBL_IR_Regions values(2,'Mazandaran',30,'NORTH');
ORA-11534: check constraint (SYS.SYS_C008304) due to domain constraint SYS.SYS_DOMAIN_C0040 of domain SYS.IR_REGIONS violated
SQL> insert into TBL_IR_Regions values(2,'Mazandaran',3098755,'NORTH');
1 row created.
SQL> insert into TBL_IR_Regions values(3,'Mazandaran',30987588,'SOUTH');
ORA-11534: check constraint (SYS.SYS_C008303) due to domain constraint SYS.SYS_DOMAIN_C0041 of domain SYS.IR_REGIONS violated
Comments
Post a Comment