Oracle Object Table Constraints
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
 
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 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx