I've got 2 tables in my Database... These two tables are linked via one-to-one relationship... Now i want to delete record thats in both tables, how do i do this? if i try to delete it from one it will tell me it cant be deleted because its being used by another table!
how do i go about this? I'm using MS Access
Thanks HannaH [ February 12, 2006: Message edited by: H Melua ]
I don't know anything about Access, but in general there are a few different approaches.
In many databases, when there is a parent-child relationship between two tables, enforced by a Foreign Key constraint, the constraint can be specified with something like "ON DELETE CASCADE"; then when a record in the parent table is deleted, the database will automatically delete any dependent records in any child tables.
For most database (and maybe even Access) and most constraints, you should be able to delete rows in the reverse order in which they were inserted. For example, if you insert a parent row and then a child row that has a Foreign Key reference on the parent, then you need to delete all the child rows that refer to a parent before you can delete the parent.
Another approach when you've defined a circular reference (e.g. "chicken" refers to "egg" and vice-versa) is to take advantage of transactionality and constraint deferability (if your database supports it, I don't think Access does though). First you have to enable deferability on your constraints. Then you would do something like: set autocommit off delete the chicken row delete the egg row commit (There's really no order to the deletes, the other order works too). (In general with JDBC and a database that supports transactions, you should always set autocommit off, no matter what you're doing.)
Joined: Jan 04, 2005
Oh man, you're a real rescuer, Thank you soooooooo much, the delete on cascade did the trick
Thanks again, i've been stressing like mad over this , i spent several days pulling my hair and banging my head on the wall trying to find a solution!