@LINK Directive

The @LINK directive disambiguates multiple foreign-key relationships between tables or to specify self-referencing foreign keys within the same table.

It explicitly defines which foreign key to use when joining or linking tables in both table function and duality view. By default, foreign key links are automatically inferred, so you don’t always need to specify @link. Using @link directive is necessary when there are no foreign-key constraints defined, or there are multiple foreign-key relations between the same two tables, or a table’s foreign key references itself (self-referencing).

If no foreign key constraints exist between the relevant tables, you must use the @link directive with both from and to arguments to explicitly specify the joining columns. If a foreign key constraint does exist, and there is only one possible relationship, @link is optional and Oracle will infer the appropriate columns to join on automatically. However, if there are multiple possible foreign key relationships between the same tables, or the relationship is ambiguous, use @link to specify which foreign key to use.

@link accepts either the from or to argument, or both.

Recall from the Setting up the Car Racing Dataset, that the table drivers had a foreign key team_id that references to the team_id column in the teams table.

CREATE TABLE driver
  (driver_id  INTEGER PRIMARY KEY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   points     INTEGER NOT NULL,
   **team_id**    INTEGER,
   CONSTRAINT **driver_fk FOREIGN KEY(team_id) REFERENCES team(team_id)**);

The following example uses @link directive to explicitly specify the joining columns:

Example 3-6 @link Directive Specifying the Joining Columns

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        team {
            teamId: team_id
            teamName: name
            teamPoints: points
            drivers: driver @link(from: ["TEAM_ID"], to: ["TEAM_ID"]) {
                driverId: driver_id
                driverName: name
                driverPoints: points
            }
        }
    }
');

Note that the square brackets in the @link are optional unless you specify composite joining columns. The preceding example, with or without square brackets will produce the same output:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        team {
            teamId: team_id
            teamName: name
            teamPoints: points
            drivers: driver @link(from: "TEAM_ID", to: "TEAM_ID") {
                driverId: driver_id
                driverName: name
                driverPoints: points
            }
        }
    }
');
DATA
--------------------------------------------------------------------------------
{
  "teamId" : 301,
  "teamName" : "McLaren Mercedes",
  "teamPoints" : 666,
  "drivers" :
  [
    {
      "driverId" : 101,
      "driverName" : "Lando Norris",
      "driverPoints" : 282
    },
    {
      "driverId" : 102,
      "driverName" : "Oscar Piastri",
      "driverPoints" : 384
    }
  ]
}

{
  "teamId" : 302,
  "teamName" : "Ferrari",
  "teamPoints" : 652,
  "drivers" :
  [
    {
      "driverId" : 103,
      "driverName" : "Charles Leclerc",
      "driverPoints" : 312
    },
    {
      "driverId" : 104,
      "driverName" : "Carlos Sainz Jr.",
      "driverPoints" : 340
    }
  ]
}
..............................
..............................
10 rows selected.

Note: Starting in 23.26.1, the order of arguments within a directive does not affect how the directive is applied. For example, for the @link directive, specifying to before from produces the same result as specifying from before to. This applies generally to directive arguments.

The following example uses @link to explicitly specify the joining columns between team and driver. Note that the to argument is specified before the from argument.

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        team {
            teamId: team_id
            teamName: name
            drivers: driver @link(to: "TEAM_ID", from: "TEAM_ID") {
                driverId: driver_id
                driverName: name
                driverPoints: points
            }
        }
    }
');

Executing this script would produce identical output regardless of whether the directive arguments are specified in from, to order or to, from order.

@link directive is commonly used in creating JSON-Relational Duality View using GraphQL. See this section for a detailed example.

To understand this scenario, create and insert data a new table that has a foreign-key that references to the same table.

Example 3-7 Create and Insert Data to a New Table that has a Self-Referencing Field

CREATE TABLE driver_w_lead
  (driver_id  INTEGER PRIMARY KEY,
   name       VARCHAR2(255) NOT NULL UNIQUE,
   points     INTEGER NOT NULL,
   team_id    INTEGER,
   lead_driver_id INTEGER,
   CONSTRAINT driver_w_lead_team_fk FOREIGN KEY(team_id) REFERENCES team(team_id),
   CONSTRAINT driver_w_lead_fk FOREIGN KEY(lead_driver_id) REFERENCES driver_w_lead(driver_id));

INSERT INTO driver_w_lead (driver_id, name, points, team_id, lead_driver_id) VALUES
  (101, 'Lando Norris', 282, 301, NULL),
  (102, 'Oscar Piastri', 384, 301, 101),
  (103, 'Charles Leclerc', 312, 302, NULL),
  (104, 'Carlos Sainz Jr.', 340, 302, 103),
  (105, 'Max Verstappen', 456, 303, NULL),
  (106, 'Sergio Pérez', 133, 303, 105),
  (107, 'Lewis Hamilton', 240, 304, NULL),
  (108, 'George Russell', 228, 304, 107),
  (109, 'Fernando Alonso', 58, 305, NULL),
  (110, 'Lance Stroll', 36, 305, 109),
  (111, 'Esteban Ocon', 33, 306, NULL),
  (112, 'Pierre Gasly', 32, 306, 111),
  (113, 'Nico Hülkenberg', 30, 307, NULL),
  (114, 'Kevin Magnussen', 28, 307, 113),
  (115, 'Daniel Ricciardo', 24, 308, NULL),
  (116, 'Yuki Tsunoda', 22, 308, 115),
  (117, 'Alexander Albon', 12, 309, NULL),
  (118, 'Logan Sargeant', 5, 309, 117),
  (119, 'Valtteri Bottas', 3, 310, NULL),
  (120, 'Zhou Guanyu', 1, 310, 119);
SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query getDriversWithLeadDriver {
        driver_w_lead {
            id: driver_id
            name
            points
            team @unnest {
                teamName: name
            }
            driver_w_lead @link(from: lead_driver_id) @unnest {
                leadDriver: name
            }
        }
    }
');