RDBPROD: DOMAIN Constraints are not propagated or created in script from RMU/EXTRACT
Last updated on MAY 25, 2017
Applies to:
Oracle Rdb Server on OpenVMS - Version 7.1 to 7.2.5 [Release 7.1 to 7.2]HP OpenVMS Itanium
HP OpenVMS Alpha
Symptoms
Domain constraints are not being propagated in the script created by an RMU/EXTRACT.
RMU/EXTRACT/ITEM=(tables)/OPTIONS=(nodomains)/LANG=ANSI does not take the default and/or validation of domains and propagate it to every column used by the domain.
SQL> show domain col1_dom;
COL1_DOM CHAR(1)
Valid If: (((value is null)
or SUBSTRING(value from 1 for 1) between 'A' and 'Z'))
SQL> SHOW DOMAIN COL2_DOM;
COL2_DOM CHAR(1)
SQL> SHOW TABLE TBL1;
Information for table TBL1
Columns for table TBL1:
Column Name Data Type Domain
----------- --------- ------
COL1 CHAR(1) COL1_DOM
COL2 CHAR(1) COL2_DOM
Check constraint COL2_VALUES
Domain constraints referenced by TBL1:
COL1_DOM
Valid If: (((value is null)
or SUBSTRING(value from 1 for 1) between 'A' and 'Z'))
Used by column COL1
Table constraints for TBL1:
COL2_VALUES
Check constraint
Column constraint for TBL1.COL2
Evaluated on COMMIT
Source:
CHECK (col2 IN ('M', 'F') OR col2 IS NULL )
COL1_DOM CHAR(1)
Valid If: (((value is null)
or SUBSTRING(value from 1 for 1) between 'A' and 'Z'))
SQL> SHOW DOMAIN COL2_DOM;
COL2_DOM CHAR(1)
SQL> SHOW TABLE TBL1;
Information for table TBL1
Columns for table TBL1:
Column Name Data Type Domain
----------- --------- ------
COL1 CHAR(1) COL1_DOM
COL2 CHAR(1) COL2_DOM
Check constraint COL2_VALUES
Domain constraints referenced by TBL1:
COL1_DOM
Valid If: (((value is null)
or SUBSTRING(value from 1 for 1) between 'A' and 'Z'))
Used by column COL1
Table constraints for TBL1:
COL2_VALUES
Check constraint
Column constraint for TBL1.COL2
Evaluated on COMMIT
Source:
CHECK (col2 IN ('M', 'F') OR col2 IS NULL )
The extract does not reference the domain constraint for COL1, as shown in original table definition.
RMU/EXTRACT/ITEM=(tables,constraints)/OPTIONS=(nodomains,nohead)/LANG=ANSI FOO1
create table TBL1 (
COL1 CHAR (1),
COL2 CHAR (1)
constraint COL2_VALUES
check((TBL1.COL2 in ('M', 'F')
or (TBL1.COL2 is null)))
not deferrable)
;
create table TBL1 (
COL1 CHAR (1),
COL2 CHAR (1)
constraint COL2_VALUES
check((TBL1.COL2 in ('M', 'F')
or (TBL1.COL2 is null)))
not deferrable)
;
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms