# Creating and Editing Relationships

An entity relationship represents the association between two tables that exist within a database. For example, a table containing employee data is linked to one containing sales data in the sense that each sale is made by an employee. Each relationship is denoted by a foreign key, which is an attribute that refers to the primary key of the parent entity. In a data model, these relationships are visualized through links connecting related entities.

In this article, we will discuss how you can create and edit relationships between the entities present in a data model in Astera Data Stack.

### Video

{% embed url="<https://youtu.be/_1eVZjXn9G0>" %}

### Editing an Existing Relationship

Here, we have a couple of entities that have been reverse engineered from an existing database. The entity named *Employee* is the parent entity and the one named *Sale* is the child entity.

![01-entity-relationship-employee-sales](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FK0GK9iXZpLPLP0Vgcmpe%2F01-entity-relationship-employee-sales.png?alt=media\&token=33a9dd96-7456-42c3-9117-d9233a3f21d8)

To edit a relationship, right-click on the relationship link and select *Edit* from the context menu.

![02-edit-entity-relationships](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FScpmIlp0tvgVPdHyKtKU%2F02-edit-entity-relationships.png?alt=media\&token=b59113a9-11df-4bb5-88a0-0c52e9cd6442)

The *Edit Relationship* configuration window will appear.

![03-edit-relationship-window](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FLnMBhtXS7ln6A98KTSkw%2F03-edit-relationship-window.png?alt=media\&token=9f8506f7-ab8b-4edc-9e23-a5eb497e1dee)

Using this window, you can modify the properties of the relationship that exists between these two entities.

#### Relationship Type

From the *Relationship Type* dropdown menu, you can choose a relationship type from two options: *Identifying* and *Non-Identifying*.

![04-edit-relationship-type](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FsSCIu9A6Rlt1l2fMo54p%2F04-edit-relationship-type.png?alt=media\&token=4588a4a3-24f0-4687-8b23-c40eeadaab22)

*Non-identifying*: A relationship where the primary key of the parent entity is included in the child entity but not as part of the primary key.

*Identifying*: A relationship where the primary key of the parent entity is included in the primary key of the child entity.

The relationship between *Sale* and *Employee* will remain non-identifying. You can also change the relationship type by right-clicking on the relationship link, hovering over the *Relationship Type* option, and selecting the appropriate choice.

![05-entity-relationship-type](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FzBOz5rDsWUoTpxzyLndF%2F05-entity-relationship-type.png?alt=media\&token=29854f85-d519-4542-b50f-3554535d260c)

#### Parent Alias, Child Alias, Constraint Name, and Verbs

Using the *Parent Alias*, *Child Alias*, *Constraint Name*, *Parent to Child Verb* and *Child to Parent Verb* text boxes, you can define certain attributes of the relationship.

![06-edit-relationship-details](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2Fj1YNpi7mr4P3HNdwJWFz%2F06-edit-relationship-details.png?alt=media\&token=2099fd07-1f9b-4bc7-87ce-2fcadacdf7ba)

*Parent Alias*: A user-defined alternative name for the parent entity.

*Child Alias*: A user-defined alternative name for the child entity.

*Constraint Name*: A user-defined name that represents the rules describing this relationship.

*Parent to Child Verb*: A user-defined phrase that describes the dynamics of the parent to child relationship.

*Child to Parent Verb*: A user-defined phrase that describes the dynamics of the child to parent relationship.

#### Mandatory

By checking or unchecking the *Mandatory* checkbox, you can define whether the relationship is mandatory or optional, respectively.

![07-mandatory-entity-relaationship](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2F2BGoRE33jrM0SbjQFwLW%2F07-mandatory-entity-relationship.png?alt=media\&token=3ad8e850-3a94-4c0a-b803-6abe10dc7fb7)

A mandatory relationship is one that requires the parent entity to participate in the relationship. On the other hand, an optional one is where participation is not compulsory. Once the mandatory or optional nature of a relationship has been defined, it is further indicated by the *Allows Null* column in the *Layout Builder* of the child entity. If the relationship is mandatory, the *Allows Null* checkbox for the foreign key that represents the relationship between the entities is automatically unchecked.

![08-mandatory-layout](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FWlL04FY7MUJl0U92EkiO%2F08-mandatory-layout.png?alt=media\&token=782870a8-fe84-4539-8ec3-32fd16ef611b)

You can also define the mandatory or optional nature of a relationship by right-clicking on the relationship link and checking or unchecking the *Mandatory* option.

![09-mandatory-entity-relationship](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2Fvp3hJ5uB4S2TYfKUboeH%2F09-mandatory-entity-relationship.png?alt=media\&token=fe460807-40cd-4d49-b2ef-83b418a51b98)

### Parent Field and Foreign Key(s)

The table at the bottom of the *Edit Relationship* screen contains a non-editable column representing the *Parent Field* and a corresponding dropdown menu for the *Foreign Key(s)*.

![10-entity-relationship-foreign-key](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FbEDC4S2ZdctY5leU3rVJ%2F10-entity-relationship-foreign-key.png?alt=media\&token=ca96efa8-d670-449f-be95-1d97d79f0ea9)

The *Parent Field* is the primary key of the parent entity and the *Foreign Key(s)* is a corresponding key that represents the relationship in the child entity.

### Creating a New Relationship

#### Non-Identifying

This is how you can create a non-identifying relationship between two entities:

1. Click on the *Link entities to create non-identifying relationships* icon in the data model menu bar.

![11-non-identifying-relationship-icon](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FtlWEH0tXlRUY46V8nr1t%2F11-non-identifying-relationship-icon.png?alt=media\&token=b14db72e-7912-47ff-9618-54ec5281d9ea)

2. Drag a relationship link from the parent entity and drop it onto the child entity.

![12-create-non-identifying-relationship](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FSMbqwi8owyfVj6BiRc8V%2F12-create-non-identifying-relationships.gif?alt=media\&token=49503d3b-ffbd-4f31-8a3d-ff3187e750a1)

#### Identifying

This is how you can create an identifying relationship between two entities:

1\. Click on the *Link entities to create identifying relationships* icon in the data model menu bar.

![13-identifying-relationship-icon](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2Fq7PSgzKB4eIifYVs6MmZ%2F13-identifying-relationship-icon.png?alt=media\&token=1c3aceac-e363-4903-a911-ca5e3237285e)

2\. Drag a relationship link from the parent entity and drop it onto the child entity.

![14-create-identifying-relationship](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FEkBQHTDERNs2FsigAE5D%2F14-create-identifying-relationships.gif?alt=media\&token=e71b932f-b293-46d9-b0d7-20b024d3e856)

#### Self-Reference

A self-reference relationship denotes that a foreign key exists within the same entity as the parent field. This is how you can create one:

1. &#x20;Click on the entity that fulfils the requirements of a self-reference relationship. This will enable the *Add Self Reference* icon in the menu bar.

![15-self-reference-relationship-icon](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2F8ZgYlXAnweRpxfSDkm6g%2F15-self-reference-relationship-icon.png?alt=media\&token=13c0d1cf-e713-4dec-ba18-6a652c3547ae)

2. Click on the *Add Self Reference* icon.

![16-create-self-reference-relationship](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FJJjyIzOB2dIpmPjobnVC%2F16-create-self-reference-relationship.gif?alt=media\&token=0201e920-7ccd-4fd0-b890-e0f934e275e8)

You’ll notice that a self-relationship link will have been created to and from the entity in question.

![17-self-reference-relationship](https://3181888596-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FzEifS4h8yurLAAwiGNX2%2Fuploads%2FEqrG1wmptUHxlNLfnJUy%2F17-self-reference-relationship.png?alt=media\&token=eaffc90e-37b9-4cb7-b9f0-f23abc4988ca)

This concludes our discussion on creating and editing relationships.