---
# System prepended metadata

title: Relations and Join
tags: [DB, postgresql, Note]

---

# Relations and Join


## Four kind of relation
- One to Many
- Many to One
- Many to Many
- One to One

All relations are implemented by foreign key

## One to Many & Many to One
use foreign key to link two tables

### CRUD of foreign key

#### Insert(Create)
- the object foreign key refered exist: success
- the object foreign key refered non exist
    - end up error
- the foreign key is NULL: insert success (value=null)

#### Delete
delete the refered object of foreign key

On Delete Options(set when create table, and can be change)
- Restrict(default) -> throw error
- No Action -> throw error
- Cascade -> delete all related object too
- Set Null -> set all related object foreign key field to null
- Set Default -> set all related object foreign key field to a default value

## Joins 

### Joins
- Produces value by merging together rows from different related tables
- Use join most times that you're asked to find data that involve multiple resource

:::info
- Table order between FROM and JOIN frequently makes a difference
- We must give context if column names collide
    - if not, it will (throw Error or return wrong data)
        - throw Error
            -  column reference "id" is ambiguous
   
- Table can be renameed using AS
:::





##
###### tags: `DB` `postgresql` `Note`