Deleted records in one-to-one tables

[nschroeder]

My db includes two tables, Employees and Users, both with a pk of EmpNum (Autonumber in Employees; Number in Users). Users is a subset of Employees, and in the Users table, the row source for EmpNum is a query of the Employees table. I had a relationship defined with a one-to-one between the two tables, but did not have Enforce Referential Integrity or Cascade selected.

The problem is, I deleted a record out of Users, but it also automatically deleted the related Employees record. Re-creating the employee was quite the task because all related records in several other tables also disappeared. I experimented by deleting the relationship between the two tables and then deleting another user, but it still deleted the related employee. How can this be? The two tables are no longer linked, other than the Users EmpNum lookup.

 

[Martyh]

Hi nschroeder,

Why not just have one table All the Employees and have an attribute in the table that defines whether the Employee is a user or not...

Just a thought...

Marty

 

[Cronk]

nschroeder

If you look in the Relationships (button on the Database Tools tab), you will probably see that there is Cascade deletes set in the join between the two tables.

 

[nschroeder]

Thanks for replies. Martyh -- for db efficiency. The data stored in Users table is only needed for users. Cronk, but please reread my 1st paragraph. I need to know how it can delete records in the other table when Cascade Delete is not selected, and in fact, the relationship itself has been deleted.

 

[Cronk]

Do you have a front end / back end situation where the relationship has been removed from one but not the other?

 

[Martyh]

I am confused you say:
>> Users is a subset of Employees

How can it still be a subset AND "The data stored in Users table is only needed for users" ?

Regards,

Marty

 

[nschroeder]

Cronk, if you're referring to split database, no, it's not split.

Marty, if it would help you, exchange the word User for Manager. All managers are employees, but all employees are not managers. Additional info is stored in the managers table that isn't needed for everyone else. However, a manager could stop being a manager and still be an employee, but if I delete a manager record, it's deleting the corresponding employee record, even though there is no link between pk's.