On Monday, 12 August, 2019 14:05, dboland9 <dbola...@protonmail.com> wrote:

>Hiking_Table                   Trails_Table
>Joining_Table
>-----------------                   -------------------------      --
>-------------------------
>hike_id     PK                   trail_id  PK
>hike_id   FK
>hike_date  TEXT             trail_name  TEXT          trail_id   FK
>hike_destination TEXT    trail_rating REAL
>hike_rating  REAL           trail_comments TEXT
>hike_comments  TEXT

>So far, so good.  I know how to create the tables.  What I am
>struggling with is how do I insert data into the joining table or
>don"t I.  Somethings I have read suggest that the joining table just
>contains references, so there is no actual insert.  A pointer to
>information how to do this would be appreciated.  As for queries, I
>think I use joins, but a pointer on how to do this would also be
>appreciated.

The problem is that you have not identified the keys properly.  What you call 
the PK is certainly a candidate, but it is not the actual key that identifies 
the unique data, but rather is rather a pseudokey identifying a row, not a set 
of unique data identifying a row.  Once you identify the actual keys, you are 
in business.  I would guess that the actual tables probably might look as 
follows (I have removed the extraneous embedding since it adds nothing except 
more typing, and changed your "date" into a starting datetime):

create table Hikes
(
  id INTEGER PRIMARY KEY,
  start TEXT NOT NULL CHECK (start IS datetime(start, '+0 days'),
  destination TEXT NOT NULL COLLATE NOCASE,
  rating REAL,
  comments TEXT,
  UNIQUE (date, destination)
);
create table Trails
(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL COLLATE NOCASE UNIQUE,
  rating REAL,
  comments TEXT
);
create table HikeTrail
(
  hike_id INTEGER NOT NULL REFERENCES Hikes,
  trail_id INTEGER NOT NULL REFERENCES Trails
);
create index HikedTrails on HikeTrail (hike_id, trail_id);
create index TrailsHiked on HikeTrail (trail_id, hike_id);

Assuming that you want to ADD a new hike on (start, destination, name), then 
you do the following sql:

BEGIN IMMEDIATE;
INSERT OR IGNORE INTO Hikes (start, destination) 
                 values (:start, :destination);
INSERT OR IGNORE INTO Trails (name) 
                 values (:name);
INSERT INTO HikeTrail (hike_id, trail_id)
       VALUES ((select id from Hikes where start=:start and 
destination=:destination),(select id from Trails where name=:name));
COMMIT;

To DELETE a hike on (start, destination, name) you do the following:

BEGIN IMMEDIATE;
DELETE FROM HikeTrail 
      WHERE hike_id = (select id 
                         from Hikes 
                        where start=:start 
                          and destination=:destination)
        AND trail_id = (select id
                          from trails
                         where name=:name);
DELETE FROM Hikes
      WHERE start=:start
        AND destination=:destination
        AND NOT EXISTS (select hike_id from HikeTrail where hike_id = id);
DELETE FROM Trails
      WHERE name=:name
        AND NOT EXISTS (select trail_id from HikeTrail where trail_id = id);
COMMIT;

>Perhaps these topics could be added to the excellent 
>http://www.sqlitetutorial.net/ .

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to