On Sat, 2013-11-16 at 12:01 -0500, Scott Castaline wrote:
I haven't worked with SQL DBs since the late 80's so I'm not sure what
I'm doing wrong here. I have created a BASE DB using the internal HSQLDB
engine for my music collection containing different media (CDs, DVDs,
Vinyl LPs, 45s and Cassette, wife had thrown out all my 8-tracks back in
'98 as well as my Reel-to-Reel tapes :=( ). I created the main table for
Album Info using a field that is auto incremented (essentially record
id) integer type, the first field and set as the Primary Key. I then
went to create another table (Tracks child of Albums) with TrackID as
the first field and Primary key, AlbumID, AlbumTitle, TrackNumber, Track
Title, Artist, Time, & Note set as Memo. When I went to set the
relations I picked the child table 1st and then I added the parent table
using the AlbumID as the field to link. It won't do it. I had it working
a few days ago the first time I did this, but when I went to upgrade
from Fedora 20 Alpha to Fedora 20 Beta I didn't realize that my backup
of the original DBs didn't work so I had to start over. I thought that's
what I had done originally when it worked, but apparently not.
So what should I be using to set the relations between child and parent?
Summarizing your two tables:
AlbumID - int, primary key
other Album only data
TrackID - int, primary key
AlbumID - foreign key, references row in Album Table
other Track only data
What you are doing with the graphical tool is set the foreign key
constraint for Track.AlbumID to be the values in Album.AlbumID. The idea
is to limit the valid values to Track.AlbumID to only those in
Album.AlbumID. This is not absolutely needed but can be very convenient
when entering data (152 is a valid AlbumID in Album but 512 is not).
When you query for all tracks for an arbitrary artist using vanilla SQL:
SELECT <fields desired> (each column must be only occur in one table)
FROM Album as a, Track as t (use the correct table names)
WHERE a.Artist = 'some artist'
AND a.AlbumID = t.AlbumID
In the FROM clause I used aliasing (AS a, AS t) to provide a short
reference to each table.
The SELECT clause must use aliasing (or full referencing) when needed to
make a column unique.
In the WHERE clause aliasing was used to make the references
unambiguous. Also, AlbumID occurs in both tables so aliasing (or full
table referencing) must be used so the query looks for AlbumIDs that
match in each table. Artist probably only exists in Album so alaising or
referencing is optional.
The above query does not require the foreign key constraint be set in
the Track table.
To unsubscribe e-mail to: firstname.lastname@example.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy