Specify User-Defined Identifying Keys

Use user-defined identifying keys when a table has no inferred primary-key or unique-key constraint, or when you need to override the inferred key.

Use the @identifiedBy directive in GraphQL, or the IDENTIFIED BY clause in SQL, to specify the fields that uniquely identify rows for a table in a duality view.

In this example, TEAM_ID does not have any primary-key or unique-key constraint defined in the database. The application enforces uniqueness for TEAM_ID.

The following GraphQL definition specifies TEAM_ID as the identifying key for table team:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_team_identity_dv AS
driver @insert @update @delete {
  _id : driver_id,
  name,
  points,
  team @unnest @insert @update @identifiedBy(key: ["TEAM_ID"])
  {
    team_id,
    teamName   : name,
    teamPoints : points
  }
};

The following SQL definition uses IDENTIFIED BY to specify the same identifying key:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW driver_team_identity_dv AS
SELECT JSON {
  '_id'    : d.driver_id,
  'name'   : d.name,
  'points' : d.points,
  UNNEST (
    SELECT JSON {
            'team_id'    : t.team_id,
            'teamName'   : t.name,
            'teamPoints' : t.points
          }
    FROM team t IDENTIFIED BY(team_id) WITH INSERT UPDATE
    WHERE t.team_id = d.team_id
  )
}
FROM driver d WITH INSERT UPDATE DELETE;

Use IDENTIFIED BY when a table has no primary-key or unique-key constraint, or when you want to override the identifying key that Oracle AI Database would otherwise infer.

The identifying key can include up to 32 columns. If the same table appears more than once in a view definition, then use the same identifying key columns for every occurrence of that table.

Note: Oracle AI Database assumes that the columns specified by IDENTIFIED BY are unique. To improve performance and enforce data integrity, create a unique index or constraint on the identifying key columns.