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

On 11/16/2013 02:25 PM, Jay Lozier wrote:
> 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,

So how would I do that in the graphical tool? I can't seem to get it to 
work. I vaguely remember working with foreign keys and the sql statement 
would refer to AlbumID from the parent as Album.AlbumID for both tables. 
Wouldn't I also need the ID from the Album table as well? Maybe I'm just 
better of using SQL Statements, but I don't see where I can do that. The 
GUI does not seem to be able to do what I am trying to do or I have 
forgotten more than I thought.


--
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