1

Given the following tables:

create table permissions(id identity);
create table companies(id identity, 
    permission_id bigint not null, 
    foreign key(permission_id) references permissions(id) on delete cascade);
create table departments(id identity, 
    company_id bigint not null, 
    permission_id bigint not null, 
    foreign key(company_id) references companies(id), 
    foreign key(permission_id) references permissions(id) on delete cascade);

When a department is deleted, I want the following statements to execute atomically:

  • The departments row is deleted
  • The permissions row associated with the department row is deleted
  • The companies row associated with the department row is deleted
  • The permissions row associated with the company (previous point) is deleted

Questions:

  1. If I use READ_COMMITTED transaction isolation and ON CASCADE DELETE on all foreign keys, are the rows deleted as a single atomic statement? Or are they executed as separate delete statements which are vulnerable to READ_COMMITTED anomalies?
  2. How do I instruct the database to delete the company/department permission when the company/department gets deleted (atomically, if possible)?
  3. Does the SQL standard say anything about this question? Or does the behavior differ across different databases?

CLARIFICATIONS:

  1. company/department need to reference the permissions table and not the other way around. This is because permissions forms a Closure Table (e.g. a user has permission to a company, and a company has permission to a department, therefore a user has permission to the department). Because the hierarchical relationship spans different types (i.e. users, companies, departments) the permissions table cannot point to a specific type. Therefore, company/department must reference a permission and not the other way around.
  2. Say I want to delete a Department, it isn't sufficient for me to delete its Permission and let CASCADE take care of the rest because it'll delete the Department's permission, the Department and Company but it'll neglect to delete the company's Permission.
  3. I cannot use a trigger to delete a permission after a company/department has been deleted because H2 runs triggers in a separate database connection than the main statement and the two request a write lock on the same company/department row. The first connection locks the companies row I am deleting. The second connection (trigger) attempts to delete the permissions row associated with the company but ON CASCADE DELETE requires it to lock the company as well. This was caused by a limitation of H2 for which there is a workaround. See https://groups.google.com/d/msg/h2-database/B3xG488RBhI/DOsIMVmPBnAJ
Community
  • 1
  • 1
Gili
  • 86,244
  • 97
  • 390
  • 689
  • Could you please clarify what you mean with "the department is deleted". Do you mean the row in the department table with the given department id is deleted? If that's the case, the statement "department.permission_id is deleted" doesn't make much sense as the whole row is deleted. Or do you mean the row should stay, and only the column permission_id should be set to null? – Thomas Mueller May 23 '13 at 18:45
  • @ThomasMueller, sorry for the confusion. Please see the revised question. – Gili May 23 '13 at 18:51

1 Answers1

0

It is atomic in most of the databases except mysql with MyISAM and perhaps MS Access, and generally you could do what you want with triggers, but It is weird because usually is the opposite what we want, I mean when one permission is deleted then the the departments that have that permission are deleted too

Manuel Alejandro Mijangos Gonz
  • 71
  • 4