On Monday, 12 August, 2019 14:05, dboland9 <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
