@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. Normally, 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 correct 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.
from- Specifies the column(s) in the source table or object from which the link originates.to- Specifies the column(s) in the target table or object to which the link connects.
from or
to to clarify the relationship. However, if there
are no foreign key constraints defined between the tables, you should
provide both from and to to explicitly
specify how the tables should be joined.
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-1 @link Directive Specifying the Joining Columns
SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
team {
teamId: team_id
teamName: name
teamPoints: points
drivers: driver @link(from: ["TEAM_ID"], to: ["TEAM_ID"]) {
driverId: driver_id
driverName: name
driverPoints: points
}
}
');@link are
optional unless you specify composite joining columns. The above example,
with or without square brackets will produce the same
output: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.@link directive is commonly used in creating JSON-Relational Duality View using GraphQL. See this section for a detailed example.
@link Directive to Identify a Foreign-Key Relation That References the Same Table
To understand this scenario, create and insert data a new table that has a foreign-key that references to the same table.
Example 3-2 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('
driver_w_lead {
id: driver_id
name
points
team @unnest {
teamName: name
}
driver_w_lead @link(from: lead_driver_id) @unnest {
leadDriver: name
}
}
');