Our organization has been using an Access database to store information about members of the board of directors. a current project involves migrating the tables to SQL Server so they can be accessed on the web. To prevent orphaned records I asked the DBA's to add referential integrity and cascading deletes. The front-end is still in Access. Now I can't delete from the board table due to foreign key constraints. Any suggestions?
Referential integrity and cascading deletes
- Thread starter mikemaki
- Start date
wiklendt
i recommend chocolate
- Local time
- Tomorrow, 08:01
- Joined
- Mar 10, 2008
- Messages
- 1,746
first, back up your database.
in relationship view you can remove the relational links between the board table and any it has links with.
but i wonder why you would need to delete this table at all if all you're doing is migrating the database to SQL server?
georgedwilkinson
AWF VIP
- Local time
- Today, 17:01
- Joined
- Mar 4, 2008
- Messages
- 3,856
A couple of things.
I assume when you say that the front end is still in Access that you mean that the back end is no longer in Access. Of course, if that is the case, you won't be able to affect any change by modifying the relationships in Access, as suggested earlier.
I'm always awed when someone believes that they need to delete records (especially records from a major table) unless there is an error that occurred or unless the database is being retrofitted. I usually keep data forever in an OLTP system and just mark out of date data with some kind of valid flag. I don't normally display data that is marked invalid and I don't delete data except as a part of a major system maintenance, just mark it as invalid.
As far as cascading deletes go: your DBAs did not set up cascading deletes (like you said you asked them to do) if you are exhibiting this problem and describing it accurately to us. I personally believe that cascading deletes in an OLTP system is generally a very bad idea.
I assume when you say that the front end is still in Access that you mean that the back end is no longer in Access. Of course, if that is the case, you won't be able to affect any change by modifying the relationships in Access, as suggested earlier.
I'm always awed when someone believes that they need to delete records (especially records from a major table) unless there is an error that occurred or unless the database is being retrofitted. I usually keep data forever in an OLTP system and just mark out of date data with some kind of valid flag. I don't normally display data that is marked invalid and I don't delete data except as a part of a major system maintenance, just mark it as invalid.
As far as cascading deletes go: your DBAs did not set up cascading deletes (like you said you asked them to do) if you are exhibiting this problem and describing it accurately to us. I personally believe that cascading deletes in an OLTP system is generally a very bad idea.
