Foreign key snippets

Foreign Keys - Add

Whalesync supports foreign keys. After creating your tables, you can designate a foreign key and then point it to another table's primary key.

ALTER TABLE public."Table"
ADD CONSTRAINT table_category_fkey
FOREIGN KEY ("category")
REFERENCES categories("name");

In the example above we are adding to a table called "Table" to an existing column called "category" a foreign key pointing to column called "name" in a table called "categories". The example creates the foreign key relationship, it does not create any tables and columns, they must already all exist.

Foreign Keys - Arrays

About foreign key arrays

Unfortunately, Postgres does not support arrays of foreign keys. Some of the other apps we connect with (e.g. Airtable) do, so we've tried to build a workaround for this. With this workaround you'll be able to sync Airtable linked records with a fake Postgres foreign key array.

How it works

At a high-level, you can configure a Postgres column to be an array BUT signal to Whalesync that you'd like us to treat it as a foreign key array.

Since Postgres just sees this as an array, the field no longer has the same validation, but it will sync.

Note

Note: a bad value here will not break Whalesync, that value will simply not sync until you correct it.

How do I implement this?

Imagine you have two tables:

• Contacts
• Company

To create a foreign key array you can:

1. Ensure both tables have a primary key (e.g. uuid)

2. In Company, create an array column "People_fk_Contacts" with type uuid[] to match the type of the table id.

   1. The "_fk" designates to Whalesync that you want this to be a foreign key.
   2. The "_Contacts" designates that it should point to the Contacts table.

   (*note - must match capitalization)

3. Set up your sync in Whalesync as normal, mapping your fields.

Example snippet

CREATE TABLE public."Contacts" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Email" text );

CREATE TABLE public."Company" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Name" text,
"People_fk_Contacts" uuid[] );