Adding an additional table and foreign keys

Let's add a second table to our database. Create a file called sql/si/todo_item.si.

Your structure should now look like this:

1
2
3
4
5
jportal2-demo
└───sql
    └───si
        ├──todolist.si
        └──todo_item.si

todo_items.si
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DATABASE ExampleDatabase
PACKAGE com.example.db
SERVER ExampleServer
SCHEMA ToDoList_App

TABLE ToDo_Item
   ID               SEQUENCE
   TodoList_ID      INT     //This is a foreign key to the ToDoList table
   ItemName         CHAR(255)
   ItemDescription  CLOB
   LastUpdated      TIMESTAMP

//This define ID as the Primary Key
KEY PKEY PRIMARY
    ID

LINK ToDoList TodoList_ID

//We do a normal Insert and Update without a Returning here, to test the regular generaion
//We do an InsertReturning and UpdateReturning in the ToDoList table to test that generation there
PROC Insert
PROC Update
PROC SelectOne
PROC DeleteOne

Most of the file should be familiar to you now, but have a look at line 17:

17
LINK ToDoList TodoList_ID```

The LINK keyword creates a foreign key. The syntax is LINK <parent_table> <my_column_name>. The line above instructs JPortal that there is a foreign key from ToDoItem.TodeList_ID to the primary key of ToDoList.