---
title: Virtual Relationships Redux
description: Gone are the days of limited directions that you could provision. Release 2.4.1 removes limitations and expands this feature to other database services.
image: https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/Screen%20Shot%202016-11-28%20at%2010_03_17%20AM.png
---

[![DreamFactory logo](https://cdn.prod.website-files.com/64ed8da8a866be7a702fbae0/68d51994d3678214b54acb60_dreamfactory-navbar-logo.svg)](https://www.dreamfactory.com/)

 Products & Services

[AI Data Gateway](https://www.dreamfactory.com/ai-data-gateway/overview)

[Overview Why DreamFactory exists](https://www.dreamfactory.com/ai-data-gateway/overview) [Data Gov, Comp, Security Policy enforcement at the API layer](https://www.dreamfactory.com/ai-data-gateway/ai-data-governance) [Standard API Layer One contract for every backend](https://www.dreamfactory.com/ai-data-gateway/standard-api-layer) [API Gateway Functionality Routing, auth, rate limits, observability](https://www.dreamfactory.com/ai-data-gateway/api-gateway-functionality) [Deployment & Integration Self-hosted, cloud, hybrid](https://www.dreamfactory.com/ai-data-gateway/on-premise-deployment-and-integration) [Developer Productivity Auto-generated, never hand-coded](https://www.dreamfactory.com/ai-data-gateway/ai-development-accelerated) [AI App Architectures Patterns for RAG, agents, MCP](https://www.dreamfactory.com/ai-data-gateway/enterprise-ai-architectures)

AI Data Models

[AIOpenAI](https://www.dreamfactory.com/use-cases/openai) [GGoogle Gemini](https://www.dreamfactory.com/use-cases/google-gemini) [CAnthropic Claude](https://www.dreamfactory.com/use-cases/anthropic-claude-landing) [LMeta Llama](https://www.dreamfactory.com/use-cases/meta-llama) [MMistral AI](https://www.dreamfactory.com/use-cases/mistral) [CoCohere](https://www.dreamfactory.com/use-cases/cohere)

Services and support

[Quickstart Service Packages Expert-led Quickstarts to production](https://www.dreamfactory.com/services-and-support/quickstart-services-packages)

API Management

[Generate & Manage REST APIs From any database, in seconds](https://www.dreamfactory.com/api-management/generate-rest-apis) [Features Security, scripting, self-hosted & more](https://www.dreamfactory.com/api-management/features) [API Generation The complete guide to auto-generated APIs](https://blog.dreamfactory.com/a-complete-guide-to-api-generation) [API Management Concepts, tools, and best practises](https://blog.dreamfactory.com/what-is-api-management-a-brief-overview-of-api-management-concepts-and-tools?_gl=1*jl0njh*_gcl_au*MjQzMjgwMTc3LjE3ODIzMjI3MzY)

 Use Cases

AI Use Cases

[AI Data Access Secure, governed reads for your LLMs](https://www.dreamfactory.com/use-cases/ai-data-access) [MCP Server Drop-in Model Context Protocol](https://www.dreamfactory.com/use-cases/mcp-server) [Legacy Modernization Wrap mainframes with REST](https://www.dreamfactory.com/use-cases/legacy-modernization) [Data Governance Audit every call, enforce every policy](https://www.dreamfactory.com/use-cases/data-governance)

[Customer Case Studies](https://www.dreamfactory.com/case-studies)

[Energy Modernization](https://www.dreamfactory.com/case-studies/energy-snowflake-modernization) [Government Modernization](https://www.dreamfactory.com/case-studies/government-mainframe-oracle-modernization) [Government Business Intelligence](https://www.dreamfactory.com/case-studies/government-sql-server-bi-analyst-queries) [Manufacturing Modernization](https://www.dreamfactory.com/case-studies/steel-manufacturing-sap-erp-modernization) [Financial Services Investor Portal](https://www.dreamfactory.com/case-studies/financial-services-sql-server-investor-portal) [Non-Profit Partner Data Sharing](https://www.dreamfactory.com/case-studies/non-profit-sql-server-partner-data-sharing) [Professional Services Exec Dashboards](https://www.dreamfactory.com/case-studies/professional-services-erp-dashboards) [Education HR and External Data Sharing](https://www.dreamfactory.com/case-studies/education-student-hr-sql-server-mysql-external-data-sharing)

 Industries

Industries

#### [Healthcare HIPAA-grade APIs across EHR, claims, and labs.](https://www.dreamfactory.com/use-cases/healthcare)

#### [Financial Services Portfolios, partners, and portals on one layer.](https://www.dreamfactory.com/use-cases/financial-services)

#### [Government Modernize mainframes without re-platforming.](https://www.dreamfactory.com/use-cases/government)

#### [Manufacturing SAP, MES, and shop-floor data, governed.](https://www.dreamfactory.com/use-cases/manufacturing)

#### [Spotlight How enterprises run on DreamFactory From healthcare to energy to finance — governance baked into every endpoint. Browse case studies →](https://www.dreamfactory.com/case-studies)

 Connectors

SQL Database

[SQL SQL Server](https://www.dreamfactory.com/connectors/sql-server) [OR Oracle](https://www.dreamfactory.com/connectors/oracle) [PG PostgreSQL](https://www.dreamfactory.com/connectors/postgresql) [My MySQL](https://www.dreamfactory.com/connectors/mysql)

NoSQL & Docs

[Dy DynamoDB](https://www.dreamfactory.com/connectors/dynamodb) [Do DocumentDB](https://www.dreamfactory.com/connectors/azure-documentdb) [Mo MongoDB](https://www.dreamfactory.com/connectors/mongodb) [Cb CouchDB](https://www.dreamfactory.com/connectors/couch-db)

Cloud Warehouses

[S3 S3](https://www.dreamfactory.com/connectors/amazon-s3) [Ab Azureblob](https://www.dreamfactory.com/connectors/azure-blob) [FS FTP/SFTP](https://www.dreamfactory.com/connectors/ftp-sftp) [LS Local Storage](https://www.dreamfactory.com/connectors/local-storage)

C & SaaS

[Sf Salesforce](https://www.dreamfactory.com/connectors/salesforce) [API REST / SOAP](https://www.dreamfactory.com/connectors/soap-to-rest)

[See all 30+ connectors](https://www.dreamfactory.com/connectors)

[Blog](https://blog.dreamfactory.com/)

[FREE 30 Minute Demo](https://www.dreamfactory.com/demo)

[![DreamFactory logo](https://cdn.prod.website-files.com/64ed8da8a866be7a702fbae0/68d51994d3678214b54acb60_dreamfactory-navbar-logo.svg)](https://www.dreamfactory.com/)

![hamburger](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/megamenu/menu-hamburger.svg) ![close](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/megamenu/close-menu.svg)

 Back to main menu

 Products & Services

 Use Cases

 Industries

 Connectors

[Blog](https://blog.dreamfactory.com/)

[FREE 30 Minute Demo](https://www.dreamfactory.com/demo)

[AI Data Gateway](https://www.dreamfactory.com/ai-data-gateway/overview)

[Overview Why DreamFactory exists](https://www.dreamfactory.com/ai-data-gateway/overview) [Data Gov, Comp, Security Policy enforcement at the API layer](https://www.dreamfactory.com/ai-data-gateway/ai-data-governance) [Standard API Layer One contract for every backend](https://www.dreamfactory.com/ai-data-gateway/standard-api-layer) [API Gateway Functionality Routing, auth, rate limits, observability](https://www.dreamfactory.com/ai-data-gateway/api-gateway-functionality) [Deployment & Integration Self-hosted, cloud, hybrid](https://www.dreamfactory.com/ai-data-gateway/on-premise-deployment-and-integration) [Developer Productivity Auto-generated, never hand-coded](https://www.dreamfactory.com/ai-data-gateway/ai-development-accelerated) [AI App Architectures Patterns for RAG, agents, MCP](https://www.dreamfactory.com/ai-data-gateway/enterprise-ai-architectures)

AI Data Models

[AIOpenAI](https://www.dreamfactory.com/use-cases/openai) [GGoogle Gemini](https://www.dreamfactory.com/use-cases/google-gemini) [CAnthropic Claude](https://www.dreamfactory.com/use-cases/anthropic-claude-landing) [LMeta Llama](https://www.dreamfactory.com/use-cases/meta-llama) [MMistral AI](https://www.dreamfactory.com/use-cases/mistral) [CoCohere](https://www.dreamfactory.com/use-cases/cohere)

Services and support

[Quickstart Service Packages Expert-led Quickstarts to production](https://www.dreamfactory.com/services-and-support/quickstart-services-packages)

AI Data Models

[Generate & Manage REST APIs From any database, in seconds](https://www.dreamfactory.com/api-management/generate-rest-apis) [Features Security, scripting, self-hosted & more](https://www.dreamfactory.com/api-management/features) [API Generation The complete guide to auto-generated APIs](https://blog.dreamfactory.com/a-complete-guide-to-api-generation) [API Management Concepts, tools, and best practises](https://blog.dreamfactory.com/what-is-api-management-a-brief-overview-of-api-management-concepts-and-tools?_gl=1*jl0njh*_gcl_au*MjQzMjgwMTc3LjE3ODIzMjI3MzY)

AI Use Cases

[AI Data Access Secure, governed reads for your LLMs](https://www.dreamfactory.com/use-cases/ai-data-access) [MCP Server Drop-in Model Context Protocol](https://www.dreamfactory.com/use-cases/mcp-server) [Legacy Modernization Wrap mainframes with REST](https://www.dreamfactory.com/use-cases/legacy-modernization) [Data Governance Audit every call, enforce every policy](https://www.dreamfactory.com/use-cases/data-governance)

[Customer Case Studies](https://www.dreamfactory.com/case-studies)

[Energy Modernization](https://www.dreamfactory.com/case-studies/energy-snowflake-modernization) [Government Modernization](https://www.dreamfactory.com/case-studies/government-mainframe-oracle-modernization) [Government Business Intelligence](https://www.dreamfactory.com/case-studies/government-sql-server-bi-analyst-queries) [Manufacturing Modernization](https://www.dreamfactory.com/case-studies/steel-manufacturing-sap-erp-modernization) [Financial Services Investor Portal](https://www.dreamfactory.com/case-studies/financial-services-sql-server-investor-portal) [Non-Profit Partner Data Sharing](https://www.dreamfactory.com/case-studies/non-profit-sql-server-partner-data-sharing) [Professional Services Exec Dashboards](https://www.dreamfactory.com/case-studies/professional-services-erp-dashboards) [Education HR and External Data Sharing](https://www.dreamfactory.com/case-studies/education-student-hr-sql-server-mysql-external-data-sharing)

Industries

#### [Healthcare HIPAA-grade APIs across EHR, claims, and labs.](https://www.dreamfactory.com/use-cases/healthcare)

#### [Financial Services Portfolios, partners, and portals on one layer.](https://www.dreamfactory.com/use-cases/financial-services)

#### [Government Modernize mainframes without re-platforming.](https://www.dreamfactory.com/use-cases/government)

#### [Manufacturing SAP, MES, and shop-floor data, governed.](https://www.dreamfactory.com/use-cases/manufacturing)

SQL Database

[SQL SQL Server](https://www.dreamfactory.com/connectors/sql-server) [OR Oracle](https://www.dreamfactory.com/connectors/oracle) [PG PostgreSQL](https://www.dreamfactory.com/connectors/postgresql) [My MySQL](https://www.dreamfactory.com/connectors/mysql)

NoSQL & Docs

[Dy DynamoDB](https://www.dreamfactory.com/connectors/dynamodb) [Do DocumentDB](https://www.dreamfactory.com/connectors/azure-documentdb) [Mo MongoDB](https://www.dreamfactory.com/connectors/mongodb) [Cb CouchDB](https://www.dreamfactory.com/connectors/couch-db)

Cloud Warehouses

[S3 S3](https://www.dreamfactory.com/connectors/amazon-s3) [Ab Azureblob](https://www.dreamfactory.com/connectors/azure-blob) [FS FTP/SFTP](https://www.dreamfactory.com/connectors/ftp-sftp) [LS Local Storage](https://www.dreamfactory.com/connectors/local-storage)

C & SaaS

[Sf Salesforce](https://www.dreamfactory.com/connectors/salesforce) [API REST / SOAP](https://www.dreamfactory.com/connectors/soap-to-rest)

[See all 30+ connectors](https://www.dreamfactory.com/connectors)

[![back arrow](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/orange-arrow.svg) Blog](https://blog.dreamfactory.com/)

# Virtual Relationships Redux

 by Terence Bennett

![calendar icon](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/calendar-icon.svg) July 6, 2018

Table of contents

RECOMMENDED ARTICLES

- [A Complete Guide to API Generation](https://blog.dreamfactory.com/a-complete-guide-to-api-generation)
- [10 Best API Management Tools](https://blog.dreamfactory.com/what-is-api-management-a-brief-overview-of-api-management-concepts-and-tools)
- [Creating a Microsoft SQL Server API in Less Than 5 minutes with DreamFactory](https://blog.dreamfactory.com/creating-a-microsoft-sql-server-api-in-less-than-5-minutes-with-dreamfactory)
- [Hasura vs. DreamFactory: A Comprehensive Comparison](https://blog.dreamfactory.com/hasura-vs-dreamfactory)
- [Build A Snowflake REST API in Less Than 5 Minutes](https://blog.dreamfactory.com/generate-a-snowflake-rest-api-in-less-than-5-minutes)

In the early part of 2016, we introduced a feature called DataMesh, also known as Virtual Relationships, or Virtual Foreign Keys. The idea behind this feature is to allow users to set up “virtual foreign keys” to relate data across tables in the same SQL database where foreign keys did not exist or were not desired, or across different SQL databases altogether. We even allowed relating a SQL database table to a MongoDB collection, opening up a whole new way for a client to retrieve all the data it needed, molded in a way that was easily consumable.

In hindsight, our first implementation was limited in the resources and directions that you could provision. Some of those limitations were:

- Only one virtual relationship could be defined per field on a particular table.
- No virtual relationship could be defined if a real foreign key constraint existed on that field.
- Relationships could only be defined from the perspective of the child to the parent, i.e. a “belongs to” relationship.
- Relationships could only be defined on SQL database services.

[Release 2.4.1](https://blog.dreamfactory.com/dreamfactory-2.4-release) removes these limitations and also expands this feature to other database services in the near future.

## Provisioning Changes

A quick look at the Schema Manager UI changes should help clarify the new provisioning changes. The biggest change is how the virtual relationships are created. Previously this was done on the Field view with a “virtual foreign key” checkbox and selection dropdown menus. Now, relationships are added via the “Relationships” area of the Table view.

![Screen Shot 2016-11-28 at 10.03.17 AM.png](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/Screen%20Shot%202016-11-28%20at%2010_03_17%20AM.png)

Clicking the “Add Virtual Relationship” button will present the user with the new interface for entering in the following information about the relationship:

![Screen Shot 2016-11-28 at 10.06.05 AM.png](https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/Screen%20Shot%202016-11-28%20at%2010_06_05%20AM.png)

- **Name (name)** - String. Auto-generated by the server based on the relationship type and designated services, tables, and fields. This is the string to use in the **related** URL parameter for record retrieval. If you wish to customize this name in the API usage, see the “Alias” attribute.
- **Alias (alias)** - String. Defaults to null. Set this to a string unique to this table, i.e. cannot be the same as any field or other relationships that are attributed to this table. If set, use this string in the **related** URL parameter for record retrieval instead of the name.
- **Label (label)** - String. Defaults to capitalized version of the name if not set. User-defined label for displaying in UI.
- **Description (description)** - String. Defaults to null. User-defined description for displaying in UI.
- **Type (type)** - Enumeration. Required. The type declares the direction of the relationship from the viewpoint of the currently selected table and behaves just as the relationships of similar types would from real SQL foreign key constraints. Possible values include: 
    - “Belongs To” (belongs\_to) - This table’s row can belong to one (or none) foreign table’s row, i.e. a Contact belonging to an Account. This relationship will return 0 or 1 related row.
    - “Has Many” (has\_many) - The reverse view of the “Belongs To”, this table’s row can “have” many foreign table rows referring to it, i.e. an Account has many Contacts. This relationship will return 0 or more.
    - “Many to Many” (many\_many)
- **Field (field)** - String. Required. The field name of the table in view that takes part in the relationship.
- **Is Virtual (is\_virtual)** - Boolean. Defaults to false. If checked (true), this relationship is virtual, otherwise this is based on a intra-database foreign key constraint. Must be true to enable the rest of the fields below, otherwise these show as read-only.
- **Reference Service (ref\_service\_id)** - Integer. Required. Defaults to same service hosting the table in view. Identifier of the database service hosting the foreign end of the relationship.
- **Reference Table (ref\_table)** - String. Required. Name of the table hosting the foreign end of the relationship.
- **Reference Field (ref\_field)** - String. Required. Name of the field of the Reference Table participating in the relationship.
- **Junction Service (junction\_service\_id)** - Integer. Required and used only if type is “Many to Many”. Identifier of the service that hosts the junction table linking both sides of the relationship.
- **Junction Table (junction\_table)** - String. Required and used only if type is “Many to Many”. The junction table linking both sides of the relationship.
- **Junction Field (junction\_field)** - String. Required and used only if type is “Many to Many”. The junction table field that points to the table in view.
- **Junction Reference Field (junction\_ref\_field)** - String. Required and used only if type is “Many to Many”. The junction table field that points to the foreign/referenced table.
- **Always Fetch (always\_fetch)** - Boolean. Defaults to false. When checked (true), this relationship is always returned regardless of URL parameter **related** value.

A look at the Schema API, shows a similar layout.

```
{
    "alias": null,
    "name": "pgsql.contact_by_assigned_to",
    "label": "Pgsql Contact By Assigned To",
    "description": null,
    "type": "belongs_to",
    "field": "assigned_to",
    "is_virtual": true,
    "ref_service_id": 31,
    "ref_table": "contact",
    "ref_field": "display_name",
    "junction_service_id": null,
    "junction_table": null,
    "junction_field": null,
    "junction_ref_field": null,
    "always_fetch": false
}
```

The relationship resources can be edited as part of the complete table in the schema API `api/v2/<service_name>/_schema/<table_name>` or from the **\_related** sub-resource of that API (new in 2.4) `api/v2/<service_name>/_schema/<table_name>/_related/[<relationship_name>]`

## Retrieving the Related Data

Once provisioned, the relationship is retrievable just like any of the relationships previously supported. Use the relationship name, or alias if provisioned, as part of the **related** URL parameter upon issuing the request, which works in GET requests as well as the other verbs where **fields** and **related** can be returned.

`GET api/v2/<service_name>/_table/<table_name>?[related=<relationship_name>]`

For example…

```
GET api/v2/mysql/_table/todo/21?related=pgsql.contact_by_assigned_to
{
    "id": 21,
    "name": "Purchase party platter",
    "complete": false,
    "assigned_to": "Jane Smith",
    "pgsql.contact_by_assigned_to": {
	    "id": 13,
	    "first_name": "Jane",
	    "last_name": "Smith",
	    "display_name": "Jane Smith",
	    "birth_date": null,
	    "last_contact": null,
	    "active": true,
	    "imageUrl": null,
	    "notes": null,
	    "created_date": "2016-11-28 20:07:50.233078",
	    "last_modified_date": "2016-11-28 20:07:50.233078",
	    "created_by_id": 1,
	    "last_modified_by_id": 1
	}
}
```

All other previously supported aspects of database relationships are available across virtual relationships as well. For more on our database services, check out the [docs](https://wiki.dreamfactory.com/DreamFactory/Features/Database).

TAGS: [Social Links Menu](https://blog.dreamfactory.com/tag/social-links-menu)

![Terence Bennett](https://blog.dreamfactory.com/hs-fs/hubfs/1624046620145.jpg?width=100&height=100&name=1624046620145.jpg)

Terence Bennett

Terence Bennett, CEO of DreamFactory, has a wealth of experience in government IT systems and Google Cloud. His impressive background includes being a former U.S. Navy Intelligence Officer and a former member of Google's Red Team. Prior to becoming CEO, he served as COO at DreamFactory Software.

 Stay Connected with   
 The Connector Newsletter!

 Subscribe to stay up-to-date with DreamFactory's latest product updates, API best practices, and tech humor in your inbox.

[![Dreamfactory Logo](https://blog.dreamfactory.com/hubfs/raw_assets/public/dreamfactory/images/megamenu/Megamenu-logo.svg)](https://www.dreamfactory.com/)

[Call Sales +1 (415) 993-5877](tel:+14159935877)

Open – Mon–Fri 9–5 PT

[FREE 30 Minute Demo](https://www.dreamfactory.com/demo)

#### Follow us

- [GitHub](https://github.com/dreamfactorysoftware/dreamfactory)
- [Facebook](https://www.facebook.com/dfsoftwareinc/)
- [X (Twitter)](https://twitter.com/dfsoftwareinc)
- [LinkedIn](https://www.linkedin.com/company/dreamfactory-software)
- [YouTube](https://www.youtube.com/c/dreamfactorysoftware)

### Features

[Features](https://www.dreamfactory.com/features) [Self hosted](https://www.dreamfactory.com/features#self) [API Generation](https://www.dreamfactory.com/features#api) [Security](https://www.dreamfactory.com/features#secure) [Customization](https://www.dreamfactory.com/features#custom) [Pricing](https://www.dreamfactory.com/pricing)

### Installers

[Linux](https://www.dreamfactory.com/features#installer) [Docker](https://www.dreamfactory.com/features#installer) [Kubernetes](https://www.dreamfactory.com/features#installer)

### API Resources

[Documentation](https://docs.dreamfactory.com/) [Case Studies](https://www.dreamfactory.com/stories) [White Papers](https://www.dreamfactory.com/resources/whitepapers) [Academy](https://www.dreamfactory.com/academy) [API Calculator](https://calculator.dreamfactory.com) [Open Source](https://github.com/dreamfactorysoftware)

### Company

[Blog](https://blog.dreamfactory.com/) [Hub](https://www.dreamfactory.com/hub) [About us](https://www.dreamfactory.com/about) [Partners](https://www.dreamfactory.com/partners) [Support](https://www.dreamfactory.com/support) [Connectors](https://www.dreamfactory.com/connectors) [Contact Us](https://www.dreamfactory.com/demo)

 © 2025 DreamFactory. All rights reserved.

[Terms of Use](https://www.dreamfactory.com/terms-of-use) [Privacy Policy](https://www.dreamfactory.com/privacy-policy) [LLMs](https://www.dreamfactory.com/llms.txt)

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Terence Bennett",
    "url" : "https://blog.dreamfactory.com/author/terencebennett"
  },
  "dateModified" : "2024-05-07T08:24:19.985Z",
  "datePublished" : "2018-07-06T11:19:21.000Z",
  "headline" : "Virtual Relationships Redux",
  "image" : [ "https://blog.dreamfactory.com/hubfs/Imported_Blog_Media/Screen%20Shot%202016-11-28%20at%2010_03_17%20AM.png" ],
  "mainEntityOfPage" : {
    "@id" : "https://blog.dreamfactory.com/virtual-relationships-redux",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://blog.dreamfactory.com/hubfs/DreamFactory%20-%20Orange%20-%20Transparent-1.png"
    }
  }
}
```