When deleting one record its deleting record from another table

[BBK]

Having problems when i delete a record from my tenants table.
Two tables one property to many tenants is my relationship with enforced referential integrity ticked and cascade update ticked and i took the cascade delete off.

Problem: When I delete a tenant from tenant table it is also deleting the whole property record from the property table also. I know it is not correct to delete records but its just in case of wrong information being recorded and its bugging me why this is happening when cascade delete is turned off.

 

[SOS]

propertyID should not be inside tblTenant.

tblLease should have the propertyID and the TenantID like it does but Tenant should not be tied to the property except through the lease.

 

[SOS]

Oh, and cascade updates are not necessary when using autonumbers as your keys. They really are in case you use something like a text field and then you have to make a change to the text (which is bad design anyway).

 

[BBK]

Thanks for the reply SOS appreciate your time for looking into it for me.

Looks like i got to redo a lot of queries now as well.

 

[SOS]

Oh, I missed one too. I think your payments should not be tied to the tenant but should be instead tied to lease ID. You then also don't need the tenant ID or name in the payments table.