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 <[email protected]> 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
