|=--------------------------------------------------=|
      _________________  .____     ___________________
     /   _____/\_____  \ |    |    \_   _____/\______ \
     \_____  \  /  / \  \|    |     |    __)_  |    |  \
     /        \/   \_/.  \    |___  |        \ |    `   \
    /_______  /\_____\ \_/_______ \/_______  //_______  /
            \/        \__>       \/        \/         \/
    
    
    |=--------------------------------------------------=|
    . |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.
    
    
    |=--------------------------------------------------=|
    |=--------------------------------------------------=|