Actually there was no problem. I just saw the terms "primary key" and "foreign key" and wasn't clear on what they meant. I have seen table layouts where people have a table just containing keys and couldn't figure out why.
But if what I'm going actually is foreign keys, then the issue for me isn't so much learning what to do, but learning the terminology properly. So you are saying that my query is actually an INNER JOIN?? That was the next thing I had to come to grips with - Inner and outer joins. I've read the part in the SQL book I have where it explains what inner and outer joins are and its been double-dutch to me the first 5 times through. So I'm already half way there and I never knew it?? KEWL! Cheers, Mike Kear Windsor, NSW, Australia AFP WebWorks -----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Sunday, 29 September 2002 12:51 AM To: SQL Subject: Re: Why External keys? Michael, now that I've seen your schema, I'm wondering what the problem is here. You *are* using foreign keys and your schema is well designed. As for your query, it's fine, too. It's perfectly acceptable, in MS SQL, to inner join tables in the WHERE clause that way. So, what was the problem again? > TABLE: dbo.COMPANY > Companyid (int) identity (1,1) NOT NULL PRIMARY KEY > Companyname varchar 150 > (Other fields address etc) > > TABLE: dbo.ARTIST > Artistid (int) identity (1,1) NOT NULL PRIMARY KEY > Artistname varchar 255 > (other fields) > > > TABLE: dbo.Album > albumid (int) identity (1,1) NOT NULL PRIMARY KEY > Albumname varchar 255 > Companyid (int) // this is the link to the company table > Artistid (int) // this is the link to the artist table > (other fields) > > > TABLE: dbo.Songs > songid (int) identity (1,1) NOT NULL PRIMARY KEY > Albumid (int) > Songname varchar 255 > (Other fields) > > > So the tables are all linked together, using the various id fields. I can > see that if I change things round a bit I can force the database not to > allow a song to be entered without having a valid link to an album and > similarly to the other tables. If I want to list out all the songs and other > details for a particular album, I use a query like this: > > SELECT Album.albumname, songs.songname, artist.artistname, > company.companyname > > FROM album, songs, artist, company > > WHERE > > Songs.albumid = album.albumid > AND > Album.artistid = artist.artistid > AND > Album.companyid = company.companyid. > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
