Oracle Object Table Constraints
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
notes
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 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 AS OBJECT (
name         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 (name))
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
When a REF value points to a nonexistent object, the REF is said to be "dangling". A dangling REF is different from a null REF. To determine whether a REF is dangling or not, use the condition IS [NOT] DANGLING. standard.'IS DANGLING'(B REF "<ADT_1>") RETURN BOOLEAN;

standard.'IS NOT DANGLING'=(B REF "<ADT_1>") RETURN BOOLEAN;
conn oe/oe

SELECT o.customer_ref.cust_email
FROM oc_orders o
WHERE o.customer_ref IS NOT DANGLING;

SELECT o.customer_ref.cust_email
FROM oc_orders o
WHERE o.customer_ref IS 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;

SELECT *
FROM user_refs;

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

DROP TABLE address_table PURGE;

SELECT *
FROM customer_addresses ca
WHERE ca.address IS DANGLING;
 
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

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