Re: [libreoffice-users] RE: Base; Setting up relations between tables

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
Scott

Summarizing your two tables:

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

Track Table
TrackID - int, primary key
AlbumID - foreign key, references row in Album Table
TrackNumber
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
jslozier@gmail.com


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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