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


|=--------------------------------------------------=|
. |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.


|=--------------------------------------------------=|
; v o | Thanks Linux and Vim | Visit SQLOK | Donate  ;
|=--------------------------------------------------=|