Creating relationship between user and book in apache derby db

Question

I am joining tables with derby database. I want to create tables with one to many relation.

What is the syntax of creating foreign key in apache derby? I am using JDBC but cannot join two tables

Answer 1

You can use the REFERENCES clause to establish a foreign key relationship between two tables.

For example:

create table app_user (
  id int primary key not null,
  first_name varchar(20) not null,
  last_name varchar(20) not null
);

create table book (
  id int primary key not null,
  title varchar(20) not null,
  user_id int not null references app_user (id)
);

In this case:

• the column user_id in the table book will be the "reference part" of a foreign key relationship between both tables.
• the column id in the table app_user will be the "key part" of a foreign key relationship between both tables.
• the foreign key is unnamed, and exists between both tables; it's not the column user_id in book.
• the foreign key enforces restrictions to the reference part and also to the key part.

If you want to join the tables the simplest join can be done using the foreign key. For example

select u.*, b.*
from app_user u
join book b on b.user_id = u.id