SFDC Foreign Key Definitions

Add foreign key definitions to DDL

Order the CREATE TABLE statements correctly

Paste updated DDL in a secret gist

Add foreign key definitions to DDL

Here is the DDL for a Postgres schema that is used for replicating data from Salesforce into Postgres.

This schema has no foreign keys defined. Please add the foreign key definitions using the Salesforce System Fields documentation and the Salesforce Standard Objects documentation. Any field that is documented as being a reference is a foreign key:

Example:

Here is the DDL for the AccountHistory object.

According to the documentation for the system fields, the "createdbyid" column is a foreign key reference to "user.id":

Additionally, according to the documentation for the AccountHistory object, the "accountid" column is a foreign key reference to "account.id":

Therefore, the updated DDL should include two foreign key definitions for these columns:

CREATE TABLE salesforce.accounthistory( 
    id VARCHAR(18) PRIMARY KEY, 
    isdeleted BOOLEAN, 
    accountid VARCHAR(18) references salesforce.account(id), 
    createdbyid VARCHAR(18) references salesforce.user(id), 
    createddate TIMESTAMP WITHOUT TIME ZONE, 
    field VARCHAR(765), 
    oldvalue VARCHAR(765), 
    newvalue VARCHAR(765)
)
;

Order the CREATE TABLE statements correctly

Once the foreign key definitions have been added in step 1, the CREATE TABLE statements will likely need to be reordered so that they execute without any errors. If there happens to be a circular foreign key dependency please resolve it using this method.

Most likely this will require a Postgres instance or the use of sqlfiddle to test that it is correct.

Example:

In the example in step 2, we added a foreign key from "accounthistory.createdby_d" to "user.id". However, as it stands in the current DDL script, the "user" table is created after the "accounthistory" table. Therefore, the CREATE TABLE statements for these tables need to be reordered as follows:

CREATE TABLE salesforce.user( 
    id VARCHAR(18) PRIMARY KEY, 
    username VARCHAR(240), 
    lastname VARCHAR(240),
    ...
)
;

CREATE TABLE salesforce.accounthistory( 
    id VARCHAR(18) PRIMARY KEY, 
    isdeleted BOOLEAN, 
    accountid VARCHAR(18) references salesforce.account(id), 
    createdbyid VARCHAR(18) references salesforce.user(id), 
    createddate TIMESTAMP WITHOUT TIME ZONE, 
    field VARCHAR(765), 
    oldvalue VARCHAR(765),
    newvalue VARCHAR(765)
)
;