Cascade delete to a point?

[acrowe97]

I have a database that contains info on tenants in a building. My current relationship leads as follows: Tenant ID is a foreign key in a table with a Lease ID, which in turn is a foreign key in a table with a Suite number. Suite numbers generally do not change, but can. When a tenant moves/leaves the building, I want to be able to delete the Tenant ID and Lease ID, without deleting the info on the suite number, as the suite number is used as a foreign key in about 7 other tables. Any way I can do this? I can post pictures of my relationships if need be. Thanks.

[ranman256]

tBldg table should have an Suite# and TenantID field and LeaseID. Set [TenantID] and [LeaseID] to null with an update query.
user selects the bldg and apt#, clicks button to Remove Tenant, then it runs the query.

but the data should remain in the tBldgHist table. (for historical records)

[ssanfu]

I can post pictures of my relationships if need be.

Yes, it would help to see your tables and relationships...

[accesstos]

Deleting data it is not a good practice.
Create a table that keeps the Lease ID and some details of suites release (date, reason etc).
With an outer join on the Lease ID, you can take only those leases that have a null field on releases side.

I hope that you understand me.

[Homegrownandy]

I agree with Accesstos,

a field such as Date_Left or something like hat to indicate when they moved out. You can then exclude any records that you want.

[orange]

Here is an older draft model that dealt with Tenancy in response to a post.
It might offer some insight on relationships and serve as a starting point.

Good luck

[acrowe97]

Thank you all for the advice! Much appreciated. I have a lease end date field so I will use that as a constraint for displaying information.