RE: Could Not Delete From Specified Tables
Patty: When you go into the relationships window in Access 2000, you can add your tables (which I gather you've done) to the diagram and then specify which fields match up, whether to enforce referential integrity between them, and what join type they use. Basically once you relate a primary key to other fields, those fields are considered foreign keys to the primary. So you only want one primary key called cabinID in the cabins table. The cabinID fields in the other two tables, the photo tables, will be foreign keys, which you don't have to specify directly. Basically they can even be foreign keys if you DON'T specify a relationship between them and the primary key, however as a best practice you want to have the database do as much work as possible so you don't have to manually enforce the relationships or cascade update and delete manually within your code. I've responded more below under your questions: -Original Message- From: P@tty Ayers [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 7:59 PM To: CF-Talk Subject: Re: Could Not Delete From Specified Tables Andy - Thanks for your response. (Still welcoming any help from others as well..) Do you mean you have a primary key called photoID in this table as a primary key? cabinID in this table should be a foreign key, related to the cabins table. Okay, I just checked and no, actually I didn't make photoID in the table 'photos' a primary key. I *did* make 'otherphotoID' in 'otherphotos' a primary key. Let me see if I am understanding you - should the two secondary tables, each which have a 'cabinID' column, each have 'cabinID' as a foreigh key, and neither should have a primary key? Both should have a primary key (again as a good database design practice, but not always necessary). Name the keys whatever you want. I suggest photoID and otherphotoID, as you have it now. Both should also have cabinID as a foreign key, related to the primary key cabinID in the cabins table. And (forgive my ignorance) how do I make a foreign key? I'm looking in Access's help files and a reference book, but haven't found it yet. In the relationships window, you can specify which keys are related. You can, for instance, click on the primary key in cabins (cabinID), hold down the mouse button and drag to the foreign key in photos (cabinID). A box will pop up (I take it you might've done this already) where you can specify more options for how the fields are related, join types, and more. Sorry if I've repeated myself -- it's late and time to sleep! - Andy ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Could Not Delete From Specified Tables
Andy - Thank you very much for this response. I think I understand a lot better, and I'm reading the Access book and database theory book to get a better grip on this. I'm considering re-building my database now, which is why I'm not pursuing this particular question further. I think it could be improved upon greatly and I might be able to avoid a lot of similar problems. Thanks again, P@tty Patty: When you go into the relationships window in Access 2000, you can add your tables (which I gather you've done) to the diagram and then specify which fields match up, whether to enforce referential integrity between them, and what join type they use. Basically once you relate a primary key to other fields, those fields are considered foreign keys to the primary. So you only want one primary key called cabinID in the cabins table. The cabinID fields in the other two tables, the photo tables, will be foreign keys, which you don't have to specify directly. Basically they can even be foreign keys if you DON'T specify a relationship between them and the primary key, however as a best practice you want to have the database do as much work as possible so you don't have to manually enforce the relationships or cascade update and delete manually within your code. I've responded more below under your questions: -Original Message- From: P@tty Ayers [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 7:59 PM To: CF-Talk Subject: Re: Could Not Delete From Specified Tables Andy - Thanks for your response. (Still welcoming any help from others as well..) Do you mean you have a primary key called photoID in this table as a primary key? cabinID in this table should be a foreign key, related to the cabins table. Okay, I just checked and no, actually I didn't make photoID in the table 'photos' a primary key. I *did* make 'otherphotoID' in 'otherphotos' a primary key. Let me see if I am understanding you - should the two secondary tables, each which have a 'cabinID' column, each have 'cabinID' as a foreigh key, and neither should have a primary key? Both should have a primary key (again as a good database design practice, but not always necessary). Name the keys whatever you want. I suggest photoID and otherphotoID, as you have it now. Both should also have cabinID as a foreign key, related to the primary key cabinID in the cabins table. And (forgive my ignorance) how do I make a foreign key? I'm looking in Access's help files and a reference book, but haven't found it yet. In the relationships window, you can specify which keys are related. You can, for instance, click on the primary key in cabins (cabinID), hold down the mouse button and drag to the foreign key in photos (cabinID). A box will pop up (I take it you might've done this already) where you can specify more options for how the fields are related, join types, and more. Sorry if I've repeated myself -- it's late and time to sleep! - Andy ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Could Not Delete From Specified Tables
Ben - Thanks very much for this response. I have the feeling I've built this database incorrectly, and I'm considering re-doing it. I will keep you posted! Appreciate your help, P@tty P@tty, If you have the tables related, with 'cabins' as the "parent" table, you cannot normally delete a 'cabins' record while there exist "child" records in 'photos' or 'otherphotos' with the same key (cabinID). You can use cascading delete in Access to do this. (As in "flows downhill".) Or, you can first delete all the photos for that cabinID, and subsequently delete the cabin record. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Could Not Delete From Specified Tables
-Original Message- From: P@tty Ayers [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 3:26 PM To: CF-Talk Subject: Could Not Delete From Specified Tables Ok, you all helped me tremendously this morning, so I'm hoping someone can point me in the right direction on this new problem. I've created a Delete Record page, but when I test it, I get an error message simply saying "Could not delete from specified tables." Any ideas at all why? A little information on the database: It's in Access2000 and has three tables, 'cabins', 'photos' and 'otherphotos'. cabins has cabinID as its primary key, each rental cabin is a record. photos also has cabinID as a primary key, and I created a relationship between the two tables using the two cabinID fields. Do you mean you have a primary key called photoID in this table as a primary key? cabinID in this table should be a foreign key, related to the cabins table. otherphotos has otherphotoID as its primary key, and each record is a photo, each associated with, again, cabinID. cabins is related to otherphotos by the cabinID column. Both of the relationships are one-to-many, and I have checked "Enforce Referential Integrity" and "Cascade Update Related Fields." Are you trying to delete a cabin record? It should work then, and also cascade delete all records that contain a foreign key in the other photo tables, based on how you say you have it set up. If you DO have the cabinID set as a primary key in the photos table, this could be your problem. Your help and suggestions are GREATLY appreciated! P@tty Ayers Macromedia Evangelist - Andy ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Could Not Delete From Specified Tables
P@tty, If you have the tables related, with 'cabins' as the "parent" table, you cannot normally delete a 'cabins' record while there exist "child" records in 'photos' or 'otherphotos' with the same key (cabinID). You can use cascading delete in Access to do this. (As in "flows downhill".) Or, you can first delete all the photos for that cabinID, and subsequently delete the cabin record. HTH. Ben -Original Message- From: Andrew Tyrone [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 2:29 PM To: CF-Talk Subject: RE: Could Not Delete From Specified Tables -Original Message- From: P@tty Ayers [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 3:26 PM To: CF-Talk Subject: Could Not Delete From Specified Tables Ok, you all helped me tremendously this morning, so I'm hoping someone can point me in the right direction on this new problem. I've created a Delete Record page, but when I test it, I get an error message simply saying "Could not delete from specified tables." Any ideas at all why? A little information on the database: It's in Access2000 and has three tables, 'cabins', 'photos' and 'otherphotos'. cabins has cabinID as its primary key, each rental cabin is a record. photos also has cabinID as a primary key, and I created a relationship between the two tables using the two cabinID fields. Do you mean you have a primary key called photoID in this table as a primary key? cabinID in this table should be a foreign key, related to the cabins table. otherphotos has otherphotoID as its primary key, and each record is a photo, each associated with, again, cabinID. cabins is related to otherphotos by the cabinID column. Both of the relationships are one-to-many, and I have checked "Enforce Referential Integrity" and "Cascade Update Related Fields." Are you trying to delete a cabin record? It should work then, and also cascade delete all records that contain a foreign key in the other photo tables, based on how you say you have it set up. If you DO have the cabinID set as a primary key in the photos table, this could be your problem. Your help and suggestions are GREATLY appreciated! P@tty Ayers Macromedia Evangelist - Andy ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Could Not Delete From Specified Tables
Andy - Thanks for your response. (Still welcoming any help from others as well..) Do you mean you have a primary key called photoID in this table as a primary key? cabinID in this table should be a foreign key, related to the cabins table. Okay, I just checked and no, actually I didn't make photoID in the table 'photos' a primary key. I *did* make 'otherphotoID' in 'otherphotos' a primary key. Let me see if I am understanding you - should the two secondary tables, each which have a 'cabinID' column, each have 'cabinID' as a foreigh key, and neither should have a primary key? And (forgive my ignorance) how do I make a foreign key? I'm looking in Access's help files and a reference book, but haven't found it yet. My original message is quoted below. Thank you again, P@tty [original message] I've created a Delete Record page, but when I test it, I get an error message simply saying "Could not delete from specified tables." Any ideas at all why? A little information on the database: It's in Access2000 and has three tables, 'cabins', 'photos' and 'otherphotos'. cabins has cabinID as its primary key, each rental cabin is a record. photos also has cabinID as a primary key, and I created a relationship between the two tables using the two cabinID fields. otherphotos has otherphotoID as its primary key, and each record is a photo, each associated with, again, cabinID. cabins is related to otherphotos by the cabinID column. Both of the relationships are one-to-many, and I have checked "Enforce Referential Integrity" and "Cascade Update Related Fields." otherphotos has otherphotoID as its primary key, and each record is a photo, each associated with, again, cabinID. cabins is related to otherphotos by the cabinID column. Both of the relationships are one-to-many, and I have checked "Enforce Referential Integrity" and "Cascade Update Related Fields." Are you trying to delete a cabin record? It should work then, and also cascade delete all records that contain a foreign key in the other photo tables, based on how you say you have it set up. If you DO have the cabinID set as a primary key in the photos table, this could be your problem. Your help and suggestions are GREATLY appreciated! P@tty Ayers Macromedia Evangelist - Andy ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
