MS SQL Server: Creating relationships¶

Introduction¶

A relationship from one table/view to another can be created by defining a link between a column of the table/view to a column of the other table/view.

Typically, relationships are defined using foreign-key constraints. But in some cases, it might not be possible to use foreign-key constraints to create the relation. For example, while trying to create a relationship involving a view as foreign-keys can’t be created on views.

Using foreign keys¶

Say we created two tables, authors(id, name) and articles(id, title, content, rating, author_id).

Let us now connect these tables to enable nested queries using a foreign-key:

Step 1: Add foreign-key constraint¶

Let’s add a foreign-key constraint to the author_id column in the articles table.

Console

CLI

API

In the console, navigate to the Modify tab of the articles table. Click the Add button in the Foreign Keys section and configure the author_id column as a foreign-key for the id column in the authors table:

Create a migration manually and add the following SQL statement to the up.sql file:

ALTER TABLE articles
ADD FOREIGN KEY (author_id) REFERENCES authors(id);

Add the following statement to the down.sql file in case you need to roll back the above statement:

ALTER TABLE articles
DROP CONSTRAINT articles_author_id_fkey;

Apply the migration by running:

hasura migrate apply

You can add a foreign-key constraint using the schema_run_sql metadata API:

POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db-name>",
    "sql": "ALTER TABLE articles ADD FOREIGN KEY (author_id) REFERENCES authors(id);"
  }
}

Step 2: Create an object relationship¶

Each article has one author. This is an object relationship.

Console

CLI

API

The console infers potential relationships using the foreign-key created above and recommends these in the Relationships tab of the articles table.

Add an object relationship named author for the articles table as shown here:

You can add an object relationship in the tables.yaml file inside the metadata directory:

 - table:
     schema: public
     name: articles
   object_relationships:
   - name: author
     using:
       foreign_key_constraint_on: author_id
 - table:
     schema: public
     name: authors

Apply the metadata by running:

hasura metadata apply

You can create an object relationship by using the mssql_create_object_relationship metadata API:

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "mssql_create_object_relationship",
  "args": {
    "table": "articles",
    "name": "author",
    "source": "default",
    "using": {
      "foreign_key_constraint_on" : ["author_id"]
    }
  }
}

We can now run a nested object query that is based on this object relationship.

Fetch a list of articles and each article’s author:

query {
  articles {
    id
    title
    author {
      id
      name
    }
  }
}

query { articles { id title author { id name } } }

{ "data": { "articles": [ { "id": 1, "title": "sit amet", "author": { "name": "Anjela", "id": 4 } }, { "id": 2, "title": "a nibh", "author": { "name": "Beltran", "id": 2 } }, { "id": 3, "title": "amet justo morbi", "author": { "name": "Anjela", "id": 4 } } ] } }

Step 3: Create an array relationship¶

An author can write multiple articles. This is an array relationship.

You can add an array relationship in the same fashion as an object relationship as shown above.

Console

CLI

API

On the console, add an array relationship named articles for the authors table as shown here:

We can now run a nested object query that is based on this array relationship.

You can add an array relationship in the tables.yaml file inside the metadata directory:

  - table:
      schema: public
      name: articles
    object_relationships:
    - name: author
      using:
        foreign_key_constraint_on: author_id
  - table:
      schema: public
      name: authors
    array_relationships:
    - name: articles
      using:
        foreign_key_constraint_on:
          column: author_id
          table:
            schema: public
            name: articles

Apply the metadata by running:

hasura metadata apply

You can create an array relationship by using the mssql_create_array_relationship metadata API:

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "mssql_create_array_relationship",
  "args": {
    "table": "author",
    "name": "articles",
    "source": "default",
    "using": {
      "foreign_key_constraint_on" : {
          "table" : "articles",
          "columns" : ["author_id"]
        }
      }
    }
  }

Fetch a list of authors and a nested list of each author’s articles:

query {
  authors {
    id
    name
    articles {
      id
      title
    }
  }
}

query { authors { id name articles { id title } } }

{ "data": { "authors": [ { "id": 1, "name": "Justin", "articles": [ { "id": 15, "title": "vel dapibus at" }, { "id": 16, "title": "sem duis aliquam" } ] }, { "id": 2, "name": "Beltran", "articles": [ { "id": 2, "title": "a nibh" }, { "id": 9, "title": "sit amet" } ] }, { "id": 3, "name": "Sidney", "articles": [ { "id": 6, "title": "sapien ut" }, { "id": 11, "title": "turpis eget" }, { "id": 14, "title": "congue etiam justo" } ] } ] } }

Using manual relationships¶

Let’s say you have a table authors (id, name) and a view author_avg_rating (id, avg) which has the average rating of articles for each author.

Let us now create an object relationship called avg_rating from the authors table to the author_avg_rating view using a manual relationship:

Console

CLI

API

Step 1: Open the manual relationship section

• Open the console and navigate to the Data -> authors -> Relationships tab.
• Click on the Configure button:

Step 2: Define the relationship

The above step will open up a section as shown below:

In this case:

• Relationship Type will be: Object Relationship
• Relationship Name can be: avg_rating
• Reference will be: id -> author_avg_rating . id (similar to defining a foreign-key)

Step 3: Create the relationship

Now click on the Save button to create the relationship.

You can add a manual relationship in the tables.yaml file inside the metadata directory:

 - table:
     schema: public
     name: articles
 - table:
     schema: public
     name: authors
   object_relationships:
   - name: avg_rating
     using:
       manual_configuration:
         remote_table:
           schema: public
           name: author_average_rating
         column_mapping:
           id: author_id
 - table:
     schema: public
     name: author_average_rating

Apply the metadata by running:

hasura metadata apply

You can add a manual relationship by using the mssql_create_object_relationship metadata API:

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "mssql_create_object_relationship",
  "args": {
    "table": "authors",
    "name": "avg_rating",
    "source": "default",
    "using": {
      "manual_configuration": {
        "remote_table": "author_average_rating",
        "column_mapping": {
          "id": "author_id"
        }
      }
    }
  }
}

We can now run a nested object query that is based on this object relationship.

Fetch a list of authors with the average rating of their articles:

query {
  authors {
    id
    name
    avg_rating {
      avg
    }
  }
}

Tracking existing relationships inferred via foreign-keys¶

As mentioned in the Introduction section above, relationships can be inferred via foreign-keys that exist in your database:

Console

CLI

API

The console infers potential relationships using existing foreign-keys and recommends these on the Data -> Schema page

You can choose to track the relationships individually using the Track buttons or hit the Track all button to track all the inferred relationships in one go.

You can add relationships in the tables.yaml file inside the metadata directory:

- table:
    schema: public
    name: articles
  object_relationships:
  - name: author
    using:
      foreign_key_constraint_on: author_id
- table:
    schema: public
    name: authors
  array_relationships:
  - name: articles
    using:
      foreign_key_constraint_on:
        column: author_id
        table:
          schema: public
          name: articles

Apply the metadata by running:

hasura metadata apply

You can create multiple relationships by using the mssql_create_object_relationship metadata API and the mssql_create_array_relationship metadata API:

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bulk",
  "args": [
    {
      "type": "mssql_create_object_relationship",
      "args": {
        "table": "articles",
        "name": "author",
        "using": {
          "foreign_key_constraint_on": "author_id"
        }
      }
    },
    {
      "type": "mssql_create_array_relationship",
      "args": {
        "table": "authors",
        "name": "articles",
        "using": {
          "foreign_key_constraint_on" : {
            "table" : "articles",
            "column" : "author_id"
          }
        }
      }
    }
  ]
}