Re: SQL Property Graph Queries (SQL/PGQ)

Thanks!

The SQL standard (ISO/IEC 9075-2: Foundation) defines these terms as follows:
- *Candidate Key*: Any combination of columns that can uniquely
identify a row in a table. A table can have multiple candidate keys
(for example, a Users table might have both an EmployeeID and a
SocialSecurityNumber, both of which are unique).
- *Preferred Candidate Key*: Because a table can have multiple
candidate keys, the SQL standard designates one as the "preferred" key
to represent the table when an arbitrary unique identifier is
required.
- If the table has a defined PRIMARY KEY, that primary key is
automatically the preferred candidate key.
- If the table has no primary key but has UNIQUE constraints, the
standard uses a specific set of rules (typically the "left-most"
unique constraint in the table definition) to elect the preferred
candidate key.

I guess the reason the example works in DuckDB is that they have an
implicit RowID which can be used as a fall-back "Preferred candidate
key" to make rows in table "person_knows_person" UNIQUE.

On Tue, Mar 24, 2026 at 4:07 PM Henson Choi <assam258(at)gmail(dot)com> wrote:
>>
>> > ERROR: 42P17: no key specified and no suitable primary key exists for
>> > definition of element "person_knows_person"
>> > LINE 6: Person_knows_person
>> > ^
>> > LOCATION: propgraph_element_get_key, propgraphcmds.c:334
>> > Time: 0.459 ms
>> > ------8<------------8<------------8<------------8<------------8<------------8<------------8<------
>> >
>> > It worked when I changed person_knows_person.person1id into PRIMARY KEY.
>>
>> Yes, each element is expected to have a key, either defined explicitly
>> when creating the graph or implicitly via its primary key.
>
>
> Right. Per SQL/PGQ Subclause 9.12 "Creation of an element table
> descriptor", Syntax Rule 4:
>
> a) If <element table key clause> is not specified, then
> i) If the table descriptor of ET includes a unique constraint
> descriptor UCD that specifies PRIMARY KEY, then let ETK be
> the list of the names of the unique columns included in UCD
> in order of their ordinal position in ET.
> ii) Otherwise, the table descriptor of ET shall include a
> preferred candidate key. Let ETK be the list of columns in
> that preferred candidate key in order of their ordinal
> position in ET.
>
> b) Otherwise, let ETK be the <column name list> simply contained
> in <element table key clause>.
>
> Note: our current implementation only checks for PRIMARY KEY when
> no KEY clause is specified. The standard also allows falling back
> to a "preferred candidate key", which we don't support yet.
>
>>
>> I'm not sure whether we should infer the key from the source and
>> destination keys, though it's doable.
>
>
> "Preferred candidate key" is likely defined in SQL Foundation
> (Part 2), which I don't have access to -- I suspect it refers
> to a UNIQUE NOT NULL constraint. But if the source +
> destination key columns happen to qualify as one, they would
> already be covered by rule 4.a.ii above. So rather than
> special-casing source + destination inference, implementing
> preferred candidate key support would cover that case naturally.
>
> Regards,
> Henson