Using both integer and UUID primary keys in related PostgreSQL tables

Question

I am using Keystone.js to manage a PostgreSQL database. My database has two separate tables (Customer and Order) with a defined relationship, as outlined here:

Customer:

fields: {
  ordersPlaced: {
    type: Relationship, ref: 'Order.customer', many: true
  }
  // And some other fields
},

Order:

fields: {
  customer: {
    type: Relationship, ref: 'Customer.ordersPlaced', many: false
  }
  // And some other fields
},

Everything works as expected if both tables use auto-incrementing integers OR UUIDs as their primary keys.

However, if I use an integer ID for one table and UUID for the other (which is my preferred approach), I get the following error in the Keystone admin UI:

select "t0".* from "public"."Order" as "t0" left outer join "public"."Order" as "t1" on "t0"."ordersPlaced" = "t1"."id" where true and "t0"."ordersPlaced" = $1 - operator does not exist: uuid = integer

Is this a limitation of my setup, I.e. must related tables use the same type as their primary key? Or is it possible to use UUIDs with Customer and the default integer IDs for Order?

In either case, I'm not sure whether Keystone.js, PostgreSQL or the knex.js is the source of the error, which is making debugging difficult.

Answer 1

No primary keys of joined tables do not have the same because you typically DO NOT JOIN primary key to primary key. You join an attribute column in the child table (usually defined as a Foreign Key) to the PK of the parent. Those are the columns that must be of the same type. Your are looking for definitions something like:

create table customers( id integer generated always as identity
                      , ...
                      , constraint customers_pk
                                   primary key (id)
                      ); 
Create table orders ( id uuid default generate_random_uuid
                    , cust_id integer not null
                    , ...
                    , constraint orders_pk 
                                 primary key (id)
                    , constraint orders2cust_fk
                                 foreign key (cust_id)
                                 references customers(id)
                    ); 

then to join
select ...
  from customers  c 
  join orders o on o.cust_id - c.id 
  ,,, ;