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 ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
