Delete query versus primary key

  • Thread starter Thread starter Vladimir
  • Start date Start date
V

Vladimir

Hi friends,

I've got a problem.
I have 2 tables, both tables have an identical data structure and both have
primary key made of combination of two fields.
When I run a Delete query that should remove those records from table 1 that
are present in table 2, I get a following message:
"Could not delete from specified tables".

Might it be caused by a primary key? When I run similar query in the same
database based on tables with simple Primary key, evrything works normaly.
All permissions and Read only modes are set as needed.

I would appreciate any help.

Thanks Vladimir
 
Hi friends,

I've got a problem.
I have 2 tables, both tables have an identical data structure and both have
primary key made of combination of two fields.
When I run a Delete query that should remove those records from table 1 that
are present in table 2, I get a following message:
"Could not delete from specified tables".

Might it be caused by a primary key?
Click to expand...

I strongly suspect that it's not the key itself; it's the relationship
between the two tables. Relational integrity will prevent the deletion
of a record in Table1 if that deletion would leave an orphan record in
a related table.

You can set the relationship to enable Cascade Deletes; this will
automatically delete all linked records in Table2 when you delete a
record in Table1 (note: this can be dangerous, since you could end up
deleting a lot of data with no way to get it back!). With a multifield
key you must set the join properties on ALL of the join lines in the
relationships window.
 
John,

The problem is the tables have no related tables so that the orphan records
are ruled out. I did not manage to set any relationship between tables but
got a message instead "No unique index found for the referenced field of the
primary table."
The joke is that both tables have the primary key and indexes properly set.

When I add a column to both tables and create a record by joining Field1 of
the primary key and Field2 of the primary key like this:
StringOfField1_StringOfField2 - I get a unique field that can be a primary
key. If I create delete query with tables amended like this everything
works - and without setting any relationship. I think this is the evidence
that records are unique and there is no obstruction to accomplish the delete
query with tables based on a multifield primary key. The data in both cases
are the same.

When I want to see records selected by the query and prepared for deleting I
can, but when I run the query I get the message mentioned before.

What can I do?

Thanks Vladimir
 
When I want to see records selected by the query and prepared for deleting I
can, but when I run the query I get the message mentioned before.

What can I do?
Click to expand...

Please post the SQL of the query. You *DO* have a relationship (not an
enforced one but a relationship created for the query) - it may be
something wrong with the Join clause.
 
I've already found out the problem.
In one of the tables was an index that was somehow corrupted. So I removed a
primary key from a table - it should remove the index consequently - and
saved the table. Next step was creating a primary key again - it should
create the index again.
From this point everything became to work properly.
Thanks for a hint.

Vladimir
 
Back
Top