[PROPOSAL] Feed primary key field with sequence.nextval and foreign key with sequence.currval
- Dominant language
- Go
- Stars
- 23
- Forks
- 5
- PR merge metrics
- No merged PRs in 30d
Description
## Context
We have to insert the jsonobject in a table film
```
{
"film_id": 452
"title" : "The Matrix"
"year": 1999
}
```
## Problem
During a dataset insert primary key have to be unique to avoid conflict with existing values. Also sequences have to be updated upper than the maximum primary key inserted to avoid future conflict.
## Solution
Add sequence information in `table.yaml` and use it during `lino push insert` process.
```yaml
version: v1
tables:
- name: film
keys:
- film_id
sequence :
film_id: sequence_film_id
```
If the `film_id` is omitted, lino use the value of `sequence_film_id.nextval` to feed the primary_key.
## Relation
If the primary key is a part of a relation
```yaml
- name: film_film_category
parent:
name: film
keys:
- film_id
child:
name: film_category
keys:
- film_id
```
inserting the following JSON object
```json
{
"title" : "The Matrix",
"year": 1999,
"film_film_category" : [
{
"category_id": 151
},
{
"category_id": 452
},
]
}
```
will produce the sql statements
```sql
insert into film (film_id, title, year)
values (sequence_film_id.nextval, 'The Matrix', 1999);
insert into film_category (film_id, category_id)
values (sequence_film_id.currval, 151 );
insert into film_category (film_id, category_id)
values (sequence_film_id.currval, 452 );
```
Contributor guide
Assessment
This issue has not been assessed yet.