# 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`