_________________ .____ __________________

|=--------------------------------------------------=|
_________________ .____ ___________________
/ _____/\_____ \ | | \_ _____/\______ \
\_____ \ / / \ \| | | __)_ | | \
/ \/ \_/. \ |___ | \ | ` \
/_______ /\_____\ \_/_______ \/_______ //_______ /
\/ \__> \/ \/ \/

|=--------------------------------------------------=|
. |03.07 - Relations .
|=--------------------------------------------------=|

In this part we will examine the possibility to
relate two entities. In Relational Database this is
done by relationships implemented by foreign keys.

The primary key of a table prevents duplicity in
given column. Foreign key adds relation to two
tables. The relation can have type of 1:N (one to
many), M:N (many to many). The relation references on
table to another.

Let's start with a simple example of foreign key
definition.

Previously we created the USERS table with 4 rows.
Now we will create user's notes. We will create new
table that references the users table in the one to
many relationship.

Like that one user can have many notes. This can be
done by following query:

create table notes (
no_key integer primary key autoincrement,
no_text text,
user_key,
foreign key (user_key) references users(user_key)
);

Now we will populate notes with some data.

insert into notes (
no_text,
user_key
) values (
'Hello World!',
1
);

This will create a notes for user with user_key 1.

We will now try to test if the constraint is well
defined. But prior to this we must tell sqlite to
verify (check) the foreign constraint validity.

To enable constraints validation use:

PRAGMA foreign_keys = ON;

And then we will try to insert notes to
non-existing user:

insert into notes (
no_text,
user_key
) values (
'Hello World!',
5
);

This will result in error message:

FOREIGN KEY constraint failed

The reason for this message is try to insert notes
for user with user_key 5. And in table users we have
the highest user's key 4.

As the last task for this part we will add some
notes to the notes table. Let's generate some records
for this purpose:

with recursive
cnt(x) as (
values(1)
union all
select x+1
from cnt
where x < 8
)
select
'insert into notes (' ||
' no_text, user_key' ||
' ) values ('||
'''Notes no. ' || x || ''',' ||
((x+1) % 3 + 1) || '); ' qry
from cnt;

That'll give us some data to insert:

qry
------------------------------------------------
insert into notes ( no_text, user_key )
values ('Notes no. 1',3);
insert into notes ( no_text, user_key )
values ('Notes no. 2',1);
insert into notes ( no_text, user_key )
values ('Notes no. 3',2);
insert into notes ( no_text, user_key )
values ('Notes no. 4',3);
insert into notes ( no_text, user_key )
values ('Notes no. 5',1);
insert into notes ( no_text, user_key )
values ('Notes no. 6',2);
insert into notes ( no_text, user_key )
values ('Notes no. 7',3);
insert into notes ( no_text, user_key )
values ('Notes no. 8',1);

Now we will execute the queries and prepare some
data for the next part. In the next part we will
examine the possibilities to join data from multiple
tables.

|=--------------------------------------------------=|
|=--------------------------------------------------=|