> Thank you Steven and Diana, I understand the relational concept. I saw this > the first time in 1983. What I'm working on his what the foreign key > concept is about, rather than using primary keys in each table.
I have > relational structures in my tables now, but each table has its own primary > key. And the tables are related, and the data is not repeated. > > What I'm unsure about is how it would be different if I was not using the > primary key setup I use now. To use the example I've already mentioned, in > my music database, I have tables like this: > > 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. > > > > > What I'm not sure about is what would be different if I had foreign keys, > aside from being able to force the relationships. (which I admit is a real > biggie!) > > > Cheers, > Mike Kear > Windsor, NSW, Australia > AFP WebWorks > > > > > -----Original Message----- > From: Stephen Kellogg [mailto:[EMAIL PROTECTED]] > Sent: Saturday, 28 September 2002 5:26 AM > To: SQL > Subject: RE: Why External keys? > > Mike, > There are actually many reasons to do this. One of the main concepts behind > what is formally know as Database Normalization is that you don't repeat > data in a table. It also helps you provide referential integrity. In most of > the examples given, this helps you not have to store the customer data over > and over again in say the Orders table. There are other examples of how > Database Normalization. For instance, in your example, if you store the > actual NAME of the record company (say Super Sounds XY) in a field in the > artists table and you wanted to make a change to that record company name > (from Super Sounds XY to Sonic Sounds ABC), you would have a bunch of > records in the artists table to update. If instead, you have a Record > Company table (as you do) and rather than storing the company Name in the > artists table, you store the Primary Key from the Record Company table, all > that gets stored in your artists table is a number. Now, all you have to > change is one field in one record (the Record Company's name in the record > companies table). Since all the artists reference a number (that doesn't > need to be changed) they "automagically" get the name change. Also, indexing > and searching a int field is faster than a str field. Another reason is for > speed in code. For instance, on a form you can display a drop down box from > the data in the Record Company Table. This can be generated by searching a > smaller table (the Record Company Table) that only contains one copy of each > Record Company. This query could be cached as it probably doesn't change > very often. Then on the action page, you are searching the artists table > where the tblArtist.intRecordCompanyIDFK = form.intRecordCompanyID. This > will be comparing two numbers instead of two strings. > As with many things there are differing opinions as to how far to go with > normalization and whether to do the referential integrity in the code or the > db. I prefer to do both but that is because if you do it in the database, it > forces you to do it in the code. This helps the developer to not forget > dependencies etc. DB Normalization not only helps with deletes but with > adding records as well. You can't add an artist and say he/she is with > Company 123 if Company 123 isn't a valid company in the Record Company > table. > > For more on this, do a search on Google for Database Normalization. (watch > the wrap in the following url) > http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=database+n ormalizatio > n > > as a side note, there are many opinions of naming conventions, I use > intTableNameID for the primary key and intTableNameIDFK for the referencing > foreign key. > > HTH > > Stephen > > -----Original Message----- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: Friday, September 27, 2002 12:51 PM > To: SQL > Subject: RE: Why External keys? > > > Not such a bad thing Michael - I have worked at plenty of companies that do > not enforce foreign keys in the db - they leave it to business objects to do > it. You can also enforce triggers that do cascading deletes where, if a > company is removed it automatically removes all employees to maintain > integrity although this, in my opinion, is bad practice. > > -----Original Message----- > From: Michael Kear [mailto:[EMAIL PROTECTED]] > Sent: 27 September 2002 17:43 > To: SQL > Subject: RE: Why External keys? > > > AH!!!!!! <blinks in the sudden blast of light!> > > I've been doing that programmatically up to now. Looks like I had better > read up some more on this whole business. > > > For example, I have a music catalogue app, where I have an artists table, an > albums table, a songs table and a recordcompany table, and there are links > between them. I've written my interface, so that I have to remember to add > the company first, then the artist, then the album , then the songs, and if > I want to delete, I have to go back along the chain the other way .. songs, > then album, then artist, then company. > > > SO if I set up the foreign keys right, I could for example delete a record > company and all the other records in the other tables that depend on the > company would also be deleted? Poof! Vanish that company's albums, > artists and songs? > > > Hmmm looks like I've been doing a lot of re-inventing the wheel. > > > Cheers, > Mike Kear > Windsor, NSW, Australia > AFP WebWorks > > > > > > -----Original Message----- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: Saturday, 28 September 2002 2:22 AM > To: SQL > Subject: RE: Why External keys? > > A primary key simply enables you to uniquely define a record in a table. A > foreign key is entirely different > > Say you have 2 tables. One called company and another called employees. > Say there is a field called companyid in table "company" which is the > primary key - i.e. uniquely identifies each company. Then in the employees > table there is a column called employeeid that uniquely defines each > employee. There is also a column called companyid that identifies which > company each employee belongs too. > > This makes the Companyid field in the employee table a foreign key - i.e. > each id in the companyid field in employees references back to a company in > the companies table. However this is NOT enforced in the database yet. If > you delete a record from the companies table there will be employees who are > linked to a companyid that no longer exists - i.e. data integrity has been > broken. Therefore f you get the database to force the foreign key it will > prevent you from deleting companies where there are employees for that > company in the company table > > Make sense? > > -----Original Message----- > From: Michael Kear [mailto:[EMAIL PROTECTED]] > Sent: 27 September 2002 17:15 > To: SQL > Subject: RE: Why External keys? > > > Yes, thanks Andy, but if you have a primary key, do you NEED that other > table? Or are you not referring to the key table? > > > Sorry, but am I being dense? > > > Cheers, > Mike Kear > Windsor, NSW, Australia > AFP WebWorks > > > -----Original Message----- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: Saturday, 28 September 2002 2:01 AM > To: SQL > Subject: RE: Why External keys? > > You do this so that referential integrity is enforced by the database i.e. > you don't end up with recs in one table with no corresponding rec in > another. > > -----Original Message----- > From: Michael Kear [mailto:[EMAIL PROTECTED]] > Sent: 27 September 2002 16:54 > To: SQL > Subject: Why External keys? > > > Sorry if this is such a basic question as to be stupid, but why do you > sometimes have foreign keys? I've looked at MS's Books Online, but that > only tells me how to do it, not why I'd want to, which is typical of > Microsoft's documentation. > > > On my tables in MS SQL2000, I typically have an primary key ID field which > is int, identity, 1, 1 which works fine as far as I've gone, which I'll > admit isn't all that advanced. > > > There is obviously an advantage to having a foreign key, because people do > it, but I'm afraid I am too much a learner to know what the advantage is. > > > Can someone give me a quick explanation of why and/or when its better not to > have the key as a field in the table itself? > > > Cheers, > Mike Kear > Windsor, NSW, Australia > AFP WebWorks > > > > > > > > > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
