|=--------------------------------------------------=| _________________ .____ ___________________ / _____/\_____ \ | | \_ _____/\______ \ \_____ \ / / \ \| | | __)_ | | \ / \/ \_/. \ |___ | \ | ` \ /_______ /\_____\ \_/_______ \/_______ //_______ / \/ \__> \/ \/ \/ |=--------------------------------------------------=| . |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 | Thanks Linux and Vim | buy me a coffee | o |; |=--------------------------------------------------=| |