ORA-40609: Cannot Create JSON-Relational Duality View - Fix Schema Issues

ORA-40609: Cannot Create JSON-Relational Duality View

Error Overview

Error Text: ORA-40609: cannot create JSON-Relational Duality View

This error occurs in Oracle 23ai when attempting to create a JSON-Relational Duality View and the underlying schema does not meet the requirements for duality view construction. Duality views provide a JSON document interface over relational tables, but they require specific constraints and relationships to function correctly.

Understanding Duality Views

How Duality Views Work

JSON Document Interface
    |
    v
Duality View Definition
    |
    v
Relational Tables
├── Root Table (Primary Key required)
│   └── Foreign Key -> Child Table (Primary Key required)
│       └── Foreign Key -> Grandchild Table (Primary Key required)
└── All FK relationships define JSON nesting

Requirements

• Every table referenced in the duality view must have a primary key
• Foreign keys define the parent-child JSON document nesting
• Foreign key columns used for joining are automatically resolved and should not be explicitly selected as subobject fields
• Only supported column data types may be used (LONG, LONG RAW, and certain legacy types are excluded)
• DML annotations (WITH INSERT, WITH UPDATE, WITH DELETE) must use correct syntax

Common Scenarios

• Missing primary keys on one or more underlying tables
• Foreign key columns selected in subobject when they are resolved automatically from the parent
• Unsupported column types such as LONG or XMLType without proper handling
• Incorrect WITH INSERT/UPDATE/DELETE syntax in the view definition
• Circular foreign key references between tables
• Composite foreign keys not matching a unique or primary key on the referenced table

Diagnostic Steps

1. Verify Primary Keys on All Tables

-- Check if tables involved in the duality view have primary keys
SELECT
  t.table_name,
  c.constraint_name,
  c.constraint_type,
  LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS pk_columns
FROM dba_tables t
LEFT JOIN dba_constraints c
  ON t.table_name = c.table_name
  AND t.owner = c.owner
  AND c.constraint_type = 'P'
LEFT JOIN dba_cons_columns cc
  ON c.constraint_name = cc.constraint_name
  AND c.owner = cc.owner
WHERE t.owner = 'YOUR_SCHEMA'
  AND t.table_name IN ('TABLE1', 'TABLE2', 'TABLE3')  -- Replace with your tables
GROUP BY t.table_name, c.constraint_name, c.constraint_type
ORDER BY t.table_name;

2. Inspect Foreign Key Relationships

-- List foreign key relationships between tables
SELECT
  c.constraint_name AS fk_name,
  c.table_name AS child_table,
  LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS fk_columns,
  r.table_name AS parent_table,
  LISTAGG(rc.column_name, ', ') WITHIN GROUP (ORDER BY rc.position) AS referenced_columns
FROM dba_constraints c
JOIN dba_cons_columns cc
  ON c.constraint_name = cc.constraint_name AND c.owner = cc.owner
JOIN dba_constraints r
  ON c.r_constraint_name = r.constraint_name AND c.r_owner = r.owner
JOIN dba_cons_columns rc
  ON r.constraint_name = rc.constraint_name AND r.owner = rc.owner
WHERE c.constraint_type = 'R'
  AND c.owner = 'YOUR_SCHEMA'
  AND c.table_name IN ('TABLE1', 'TABLE2', 'TABLE3')
GROUP BY c.constraint_name, c.table_name, r.table_name
ORDER BY c.table_name;

-- Check that FK columns reference a primary or unique key
SELECT
  c.constraint_name,
  c.constraint_type,
  c.table_name,
  c.r_constraint_name,
  r.constraint_type AS referenced_constraint_type
FROM dba_constraints c
JOIN dba_constraints r
  ON c.r_constraint_name = r.constraint_name AND c.r_owner = r.owner
WHERE c.constraint_type = 'R'
  AND c.owner = 'YOUR_SCHEMA'
  AND r.constraint_type NOT IN ('P', 'U');  -- Problem: FK references non-PK/UK

3. Check Column Data Types

-- Identify unsupported column types for duality views
SELECT
  table_name,
  column_name,
  data_type,
  data_length
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
  AND table_name IN ('TABLE1', 'TABLE2', 'TABLE3')
  AND data_type IN ('LONG', 'LONG RAW', 'BFILE')
ORDER BY table_name, column_id;

4. Check Existing Duality Views for Reference

-- List existing duality views in the schema (Oracle 23ai)
SELECT
  view_name,
  text_length,
  text
FROM dba_views
WHERE owner = 'YOUR_SCHEMA'
  AND view_name LIKE '%_DV'
ORDER BY view_name;

Immediate Solutions

Solution 1: Add Missing Primary Keys

The most common cause is tables missing primary keys.

-- Add primary key to a table missing one
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id);

ALTER TABLE order_items ADD CONSTRAINT pk_order_items PRIMARY KEY (item_id);

-- If the table has no suitable single column, use a composite primary key
ALTER TABLE order_items ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_number);

-- If no natural key exists, add an identity column (Oracle 23ai)
ALTER TABLE legacy_table ADD (id NUMBER GENERATED ALWAYS AS IDENTITY);
ALTER TABLE legacy_table ADD CONSTRAINT pk_legacy_table PRIMARY KEY (id);

Solution 2: Fix Foreign Key Column Selection in Subobject

Foreign key columns used for joining are automatically resolved by the duality view. Explicitly selecting them in a subobject causes the error.

Incorrect Definition

-- WRONG: Selecting the FK column (customer_id) inside the nested orders subobject
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
SELECT JSON {
  '_id'       : c.customer_id,
  'name'      : c.customer_name,
  'email'     : c.email,
  'orders'    : [
    SELECT JSON {
      'orderId'     : o.order_id,
      'customerId'  : o.customer_id,   -- ERROR: FK column selected in subobject
      'orderDate'   : o.order_date,
      'totalAmount' : o.total_amount
    }
    FROM orders o WITH INSERT UPDATE DELETE
    WHERE o.customer_id = c.customer_id
  ]
}
FROM customers c WITH INSERT UPDATE DELETE;

Correct Definition

-- CORRECT: Omit the FK column (customer_id) from the nested subobject
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
SELECT JSON {
  '_id'       : c.customer_id,
  'name'      : c.customer_name,
  'email'     : c.email,
  'orders'    : [
    SELECT JSON {
      'orderId'     : o.order_id,
      'orderDate'   : o.order_date,
      'totalAmount' : o.total_amount
    }
    FROM orders o WITH INSERT UPDATE DELETE
    WHERE o.customer_id = c.customer_id
  ]
}
FROM customers c WITH INSERT UPDATE DELETE;

Solution 3: Fix WITH INSERT/UPDATE/DELETE Syntax

-- WRONG: Using commas between DML annotations
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON {
  '_id'    : o.order_id,
  'status' : o.status
}
FROM orders o WITH INSERT, UPDATE, DELETE;  -- ERROR: Commas not allowed

-- CORRECT: Space-separated DML annotations (no commas)
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON {
  '_id'    : o.order_id,
  'status' : o.status
}
FROM orders o WITH INSERT UPDATE DELETE;

-- CORRECT: Read-only view (no DML annotations)
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_readonly_dv AS
SELECT JSON {
  '_id'    : o.order_id,
  'status' : o.status
}
FROM orders o;

Solution 4: Complete Working Example

-- Step 1: Create properly constrained tables
CREATE TABLE customers (
  customer_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  customer_name VARCHAR2(100) NOT NULL,
  email         VARCHAR2(255) NOT NULL,
  CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

CREATE TABLE orders (
  order_id     NUMBER GENERATED ALWAYS AS IDENTITY,
  customer_id  NUMBER NOT NULL,
  order_date   DATE DEFAULT SYSDATE,
  total_amount NUMBER(10,2),
  status       VARCHAR2(20) DEFAULT 'PENDING',
  CONSTRAINT pk_orders PRIMARY KEY (order_id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  item_id    NUMBER GENERATED ALWAYS AS IDENTITY,
  order_id   NUMBER NOT NULL,
  product    VARCHAR2(100),
  quantity   NUMBER,
  unit_price NUMBER(10,2),
  CONSTRAINT pk_order_items PRIMARY KEY (item_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Step 2: Create a multi-level duality view
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
SELECT JSON {
  '_id'       : c.customer_id,
  'name'      : c.customer_name,
  'email'     : c.email,
  'orders'    : [
    SELECT JSON {
      'orderId'     : o.order_id,
      'orderDate'   : o.order_date,
      'totalAmount' : o.total_amount,
      'status'      : o.status,
      'items'       : [
        SELECT JSON {
          'itemId'    : i.item_id,
          'product'   : i.product,
          'quantity'  : i.quantity,
          'unitPrice' : i.unit_price
        }
        FROM order_items i WITH INSERT UPDATE DELETE
        WHERE i.order_id = o.order_id
      ]
    }
    FROM orders o WITH INSERT UPDATE DELETE
    WHERE o.customer_id = c.customer_id
  ]
}
FROM customers c WITH INSERT UPDATE DELETE;

Long-Term Solutions

1. Validate Schema Before Creating Duality Views

-- Pre-flight check: Ensure all tables have primary keys
DECLARE
  v_missing_pk NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT t.table_name
    FROM user_tables t
    WHERE NOT EXISTS (
      SELECT 1 FROM user_constraints c
      WHERE c.table_name = t.table_name
        AND c.constraint_type = 'P'
    )
    AND t.table_name IN ('CUSTOMERS', 'ORDERS', 'ORDER_ITEMS')  -- Your tables
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('MISSING PK: ' || rec.table_name);
    v_missing_pk := v_missing_pk + 1;
  END LOOP;

  IF v_missing_pk = 0 THEN
    DBMS_OUTPUT.PUT_LINE('All tables have primary keys - safe to create duality view');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_missing_pk || ' table(s) missing primary keys - fix before creating duality view');
  END IF;
END;
/

2. Establish Foreign Key Design Standards

-- Verify all FK relationships are properly defined
SELECT
  child.table_name AS child_table,
  child.constraint_name AS fk_name,
  parent.table_name AS parent_table,
  parent.constraint_name AS referenced_constraint,
  parent.constraint_type AS ref_type,
  CASE parent.constraint_type
    WHEN 'P' THEN 'OK - References Primary Key'
    WHEN 'U' THEN 'OK - References Unique Key'
    ELSE 'WARNING - Unusual reference type'
  END AS assessment
FROM user_constraints child
JOIN user_constraints parent
  ON child.r_constraint_name = parent.constraint_name
WHERE child.constraint_type = 'R'
ORDER BY child.table_name;

Prevention Strategies

1. Schema Design Best Practices

Before creating a duality view, verify:
1. Every table has a PRIMARY KEY constraint
2. Foreign keys reference PRIMARY KEY or UNIQUE KEY columns
3. No LONG or LONG RAW columns exist on referenced tables
4. No circular foreign key references between tables
5. FK columns are NOT selected in nested subobject projections

2. Development Workflow

• Design relationally first - Get your tables, primary keys, and foreign keys right before thinking about duality views
• Test incrementally - Start with a simple single-table duality view, then add nested subobjects one at a time
• Use identity columns - Oracle 23ai identity columns guarantee unique primary keys without sequence management
• Name constraints explicitly - Named constraints produce clearer error messages when debugging

3. Pre-Creation Checklist

-- Quick check: Tables, PKs, and FKs summary
SELECT
  t.table_name,
  NVL(pk.pk_name, '*** MISSING PK ***') AS primary_key,
  NVL(pk.pk_columns, 'N/A') AS pk_columns,
  COUNT(fk.constraint_name) AS fk_count
FROM user_tables t
LEFT JOIN (
  SELECT c.table_name, c.constraint_name AS pk_name,
         LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS pk_columns
  FROM user_constraints c
  JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
  WHERE c.constraint_type = 'P'
  GROUP BY c.table_name, c.constraint_name
) pk ON t.table_name = pk.table_name
LEFT JOIN user_constraints fk
  ON t.table_name = fk.table_name AND fk.constraint_type = 'R'
WHERE t.table_name IN ('CUSTOMERS', 'ORDERS', 'ORDER_ITEMS')  -- Your tables
GROUP BY t.table_name, pk.pk_name, pk.pk_columns
ORDER BY t.table_name;

Related Errors

• ORA-42647 - Missing _id field in duality view definition
• ORA-00942 - Table or view does not exist (underlying table missing)
• ORA-02291 - Integrity constraint violated - parent key not found
• ORA-00001 - Unique constraint violated (during DML through duality view)
• ORA-01031 - Insufficient privileges (missing CREATE VIEW privilege)

Quick Reference

Emergency Response Steps

1. Check the exact error details in the session or alert log for specifics
2. Verify all underlying tables have primary key constraints
3. Confirm foreign key relationships are properly defined
4. Ensure FK columns are not explicitly selected in nested subobjects
5. Validate column data types are supported
6. Check WITH INSERT/UPDATE/DELETE syntax (no commas)
7. Test with a simplified single-table duality view first

Quick Commands

-- Check for missing primary keys
SELECT table_name FROM user_tables
WHERE table_name NOT IN (
  SELECT table_name FROM user_constraints WHERE constraint_type = 'P'
);

-- Add a primary key
ALTER TABLE table_name ADD CONSTRAINT pk_table PRIMARY KEY (id_column);

-- List FK columns (do NOT select these in nested subobjects)
SELECT table_name, constraint_name, column_name
FROM user_cons_columns
WHERE constraint_name IN (
  SELECT constraint_name FROM user_constraints WHERE constraint_type = 'R'
)
ORDER BY table_name, constraint_name, position;

-- Minimal duality view test
CREATE JSON RELATIONAL DUALITY VIEW test_dv AS
SELECT JSON {'_id' : t.id, 'name' : t.name}
FROM my_table t WITH INSERT UPDATE DELETE;

Prevention Guidelines

• Always define primary keys - Every table in a duality view requires one
• Design foreign keys carefully - They define the JSON document nesting structure
• Do not select FK columns in subobjects - The view resolves them automatically from the parent
• Use identity columns - Simplifies primary key management in Oracle 23ai
• Test incrementally - Build duality views one nesting level at a time
• Validate schema first - Run pre-flight checks before creating complex duality views