Foreign Keys Snippets

Handy snippets to help you set up Postgres to work with Whalesync

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

As of version 15 Postgres supports composite primary and foreign key. Whalesync does not support composite keys. However we have received a number of request to support a foreign key of type array, which Postgres still does not support. Many users ask for this since many other connectors support arrays of foreign keys and our users want to sync with these.

We have created a mechanism for you to configure a Postgres column to be an array and signal to Whalesync to treat it as a foreign key array. Since from Postgres' perspective it is just an array, we loose the validation that any value placed in the array indeed is an id for a record in a foreign table. Despite this drawback, the functionality is stable and can be powerful. Note: a bad value here will not break Whalesync, that value will simply not sync until you correct it.

Here are the steps you need to take if you have tables Contacts and Company and want to add an array of foreign keys from one to the other:

• Make sure both tables have a proper primary key, we recommend a uuid for both.

• In table Company create a column "People" with type uuid[] to match the type of the table id. At the end of the column name add "_fk" as that will designate it as a foreign key to Whalesync. Then after that add "_Contacts" to designate that it should point to Contacts table (match capitalization). So the column will be then called "People_fk_Contacts".

• Set up your sync in Whalesync as normal. Map the foreign key array of one connector to the Postgres "People_fk_Contacts" in this connector. Save and sync.

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[] );