| Primary Key Constraint |
Object Identifier |
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. |
DANGLING |
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; |
| |
| DEREF |
| returns the object reference of argument expr, where expr must return a REF to an
object |
DEREF(<expression>) |
| 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>) |
| 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. |
REF(<correlation_variable>) |
| See Demo below |
| |
| Referential Constraint
Demo |
Foreign Key Without OID Reference |
CREATE OR REPLACE TYPE
cust_address_t 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
|
|
