Oracle Object Table Constraints
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
 
Data Dictionary Objects
ALL_REFS DBA_REFS USER_REFS
CDB_REFS REFCON$  
System Privileges
CREATE ANY TABLE CREATE TABLE  
 
Primary Key Constraint
Object Identifier CREATE TABLE <schema_name>.<table_name>OF <type_name>(
CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name>))
OBJECT IDENTIFIER IS PRIMARY KEY;
CREATE OR REPLACE TYPE address_type AUTHID DEFINER AS OBJECT (
street  VARCHAR2(30),
city    VARCHAR2(30),
state   VARCHAR2(2),
zipcode VARCHAR2(5));
/

desc address_type

SELECT text
FROM user_source
WHERE name = 'ADDRESS_TYPE';

CREATE OR REPLACE TYPE person_type AUTHID DEFINER AS OBJECT (
pername      VARCHAR2(30),
dob          DATE,
home_address address_type,
work_address address_type);
/

desc person_type

set describe depth all linenum on indent on

desc person_type

SELECT text
FROM user_source
WHERE name = 'PERSON_TYPE';

SELECT type_name, type_oid, typecode, incomplete, final, instantiable
FROM user_types;

CREATE TABLE people OF person_type (
CONSTRAINT pk_people
PRIMARY KEY (pername))
OBJECT IDENTIFIER IS PRIMARY KEY;

desc people

-- not here
SELECT table_name, nested
FROM user_tables;

-- not here
SELECT table_name
FROM user_nested_tables;

-- here
SELECT table_name, nested
FROM user_all_tables;

col object_name format a30

SELECT object_name, object_type
FROM user_objects;

-- Note: use type name from above query
SELECT text
FROM user_source
WHERE name = 'SYS_YOID0000117802$';

SELECT constraint_name, constraint_type, table_name
FROM user_constraints;
 
DANGLING
Dangling Demo CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code    VARCHAR2(10),
city           VARCHAR2(30),
state_province VARCHAR2(2),
country_id     VARCHAR2(2));
/

CREATE TABLE address_table OF cust_address_t;

INSERT INTO address_table
VALUES ('123 Main St.','98040','Mercer Island','WA','US');

CREATE TABLE customer_addresses (
add_id  NUMBER(10),
address REF cust_address_t SCOPE IS address_table);

INSERT INTO customer_addresses
SELECT 11, REF(a)
FROM address_table a;

col table_name format a20
col column_name format a13
col scope_table_owner format a5
col scope_table_name format a16

SELECT *
FROM user_refs;

TABLE_NAME           COLUMN_NAME   WIT IS_ SCOPE SCOPE_TABLE_NAME OBJECT_ID_TYPE
-------------------- ------------- --- --- ----- ---------------- ----------------
CUSTOMER_ADDRESSES   ADDRESS       NO  YES C##OE ADDRESS_TABLE    SYSTEM GENERATED
OC_ORDERS            CUSTOMER_REF  NO  NO                         SYSTEM GENERATED

SELECT *
FROM customer_addresses ca
WHERE ca.address IS DANGLING;

DROP TABLE address_table PURGE;

col address format a75

SELECT *
FROM customer_addresses ca
WHERE ca.address IS NOT DANGLING;

no rows returned.

SELECT *
FROM customer_addresses ca
WHERE ca.address IS DANGLING;

 ADD_ID ADDRESS
------- ---------------------------------------------------------------------------
     11 0000220208FE900ADE9D0E4680B7D6026FA19B089A4E49FF1955FE44CEA710F82C7393AE94
 
DEREF
Returns the object reference of argument expr, where expr must return a REF to an object standard.DEREF(r REF IN "<ADT_1>") RETURN "<ADT_1>";
See Demo below
 
MAKE_REF
Creates a REF to a row of an object view or a row in an object table
whose object identifier is primary key based		 MAKE_REF(<table_or_view_name>, <key>) RETURN REF;
SELECT MAKE_REF(oc_inventories, 3003) FROM dual;
 
REF
Takes a correlation variable (table alias) associated with a row of an object table or an object view. A REF value is returned for the object instance that is bound to the variable or row. standard.REF (item "<ADT_WITH_OID>") RETURN REF "<ADT_1>";
See Demo below
 
REFERENCES
Create a table but with a referential integrity constraint on the
REF column that references the OID column of the parent table		 <column_name> REF <data_type> REFERENCES <object_table>
CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code    VARCHAR2(10),
city           VARCHAR2(30),
state_province VARCHAR2(2),
country_id     VARCHAR2(2));
/

CREATE TABLE address_table OF cust_address_t;

CREATE TABLE customer_addresses (
add_id  NUMBER,
address REF cust_address_t REFERENCES address_table);
 
SCOPE
Restrict the scope of a REF reference to a single table <column_name> REF <data_type> REFERENCES <object_table>
CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code    VARCHAR2(10),
city           VARCHAR2(30),
state_province VARCHAR2(2),
country_id     VARCHAR2(2));
/

CREATE TABLE address_table OF cust_address_t;

CREATE TABLE customer_addresses (
add_id  NUMBER,
address REF cust_address_t SCOPE IS address_table);
 
Object Table Referential Constraints Demo
Foreign Key Without OID Reference CREATE OR REPLACE TYPE cust_address_t AUTHID DEFINER AS OBJECT (
street_address VARCHAR2(40),
postal_code    VARCHAR2(10),
city           VARCHAR2(30),
state_province VARCHAR2(2),
country_id     VARCHAR2(2));
/

CREATE TABLE address_table OF cust_address_t;

desc address_table

INSERT INTO address_table
VALUES ('123 Main St.','98040','Mercer Island','WA','US');

INSERT INTO address_table
VALUES ('1 Broadway','10202','New York','NY','US');

INSERT INTO address_table
VALUES ('2462 Edgar Crest','V6L 2C4','Vancouver','BC','CN');

SELECT * FROM address_table;

SELECT REF(tabref) FROM address_table tabref;

CREATE TABLE customer_addresses (
add_id  NUMBER(10),
address REF cust_address_t SCOPE IS address_table);

set describe depth all linenum on indent on

desc customer_addresses

INSERT INTO customer_addresses
SELECT 11, REF(a)
FROM address_table a
WHERE country_id = 'CN';

SELECT *
FROM customer_addresses;

ROLLBACK;

INSERT INTO customer_addresses
SELECT 11, REF(a)
FROM address_table a;

SELECT *
FROM customer_addresses;

col deref_add format a80

SELECT add_id, DEREF(address) DEREF_ADD
FROM customer_addresses;
 
Check Constraint
Conditions of check constraints cannot contain the following constructs:
  • Dereferencing of REF columns (for example, using the DEREF function)
  • Nested table columns or attributes
  • Calls to user-defined functions

Related Topics
Constraints
Conversion Functions (REFTOHEX)
Object Tables
Types
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx