Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

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?

Scott C


Summarizing your two tables:

Album Table
AlbumID - int, primary key
other Album only data

Track Table
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.

Jay Lozier

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.