How to create one to many in SQLITE3?

Question

How to create one to many in SQLITE3? I have 2 tables:

Mans:
_id   name
 1    antony
 2    fred

and

   point
_id   date   point   
 1     23     77
       24     99 

 2     25     78
        5     0

I don't know SQL syntax, help me , please.

Answer 1

Going by what iamkrillin wrote:

CREATE TABLE (points) points_id INT
FOREIGN KEY(man_id) REFERENCES mans(PrimaryKeyField)
ON DELETE CASCADE ON UPDATE CASCADE

Here is a real-world example. Let's say you have some people who refer business to you: your staff, your friends, local businesses where you do advertising, etc. Customers who come in are called 'referal' business. Each person only counts as one referal, but a referer may refer many referals (for example, an employee may refer 20 new customers; the employee is your referer, and the employee has made 20 referals). So, you have 1 referer and 20 referals (one-to-many):

CREATE TABLE referal(                                         
  referal_id INTEGER UNIQUE NOT NULL PRIMARY KEY,           //A customer can only be 1 referal.
  referal_method TEXT,                                      //How were they refered?  By phone?
  referer_id INTEGER ,                                      //Who refered them?
  FOREIGN KEY(referer_id) REFERENCES referer(referer_id));  //Trace more about referer.

Now, it is possible that more than one person refers a referal, but I think that it is standard business practice to only compensate a single referer. So, you never need to list two referers. This will always be a 1-to-1 or a 1-to-many relationship; therefore, you should make it a 1-to-many table. I'm not very proficient in the CASCADE stuff, but I'll try to figure out how that fits.

At first glance, it appears ON UPDATE CASCADE ON DELETE CASCADE does not belong in my answer because removing the last referal should not remove the referer.

Looking at a different example:

CREATE TABLE all_candy 
   (candy_num SERIAL PRIMARY KEY,
    candy_maker CHAR(25));

CREATE TABLE hard_candy 
   (candy_num INT, 
    candy_flavor CHAR(20),
    FOREIGN KEY (candy_num) REFERENCES all_candy
    ON DELETE CASCADE)

If you delete a hard candy from the hard_candy table, then you are also deleting it from the all_candy table because a hard candy is a type of candy, and if the type of candy has changed (for example, to discontinued candies), then a new INSERT command needs to be executed, anyway.

I ran a test case for ON UPDATE CASCADE and ON UPDATE DELETE in sqlite3, and it seems it has no effect. Perhaps they do not work with the default db engine for sqlite3, but the functionality IS listed at the official SQLite website: a very descriptive, easy-to-follow example of ON UPDATE CASCADE by sqlite.org. Have a read and see what you think.

This is the schema I used for my test case:

BEGIN TRANSACTION;
CREATE TABLE candy(id integer primary key not null, name text, description text);
INSERT INTO candy VALUES(1,'Laffy Taffy', 'Delicious, soft candy.');
INSERT INTO candy VALUES(2,'Pop Rocks', 'A candy that explodes in your mouth.');
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE hard_candy(id integer primary key not null, name text, description text, foreign key(id,name,description) references hard_candy ON DELETE CASCADE ON UPDATE CASCADE);
INSERT INTO hard_candy VALUES(2,'Pop Rocks', 'A candy that explodes in your mouth.');
COMMIT;

Ran various updates on either table's id-2 description field.

Answer 2

The syntax for this is as follows....

CREATE TABLE (MySecondTable) Foo INT FOREIGN KEY(Foo) REFERENCES MyFirstTable(PrimaryKeyField) ON DELETE CASCASDE ON UPDATE CASCASDE

only works on v3.6.1+

Here are the docs http://sqlite.org/foreignkeys.html