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