3

I have a simple schema in which soft deletes are used (that's how it is designed and that can't be changed). There are two tables that participate in the schema: Company (id, is_deleted) and Employee (id, company_id, is_deleted) where company_id ofcourse is a FK to the Company table. The rules are:

  • If a Company has is_deleted = true, then all Employee referring to that company should have is_deleted = true.
  • But an Employee may have is_deleted = true even if the parent Company has is_deleted = false.

My two problems are a) how to enforce these constraints? b) how to easiest ensure that is_deleted = true is cascaded when a Company is soft-deleted.

I added the tags postgresql and sql server because those are the databases I'm mostly interested in. If there are other solutions in other rdbms:es I'd like to hear about them too.

Paco
  • 602
  • 1
  • 9
  • 19
Björn Lindqvist
  • 19,221
  • 20
  • 87
  • 122

1 Answers1

4

Strictly speaking, the only way to cascade values like that is by using ON UPDATE CASCADE. To do that, the column "is_deleted" has to be part of a unique constraint.

That alone isn't too hard. If company.id is your primary key, then the pair of columns {id, is_deleted} will also be unique. A unique constraint on that pair of columns would allow you to cascade updates through a foreign key reference.

But that won't work in your case, because you need to allow referencing values to be different from the referenced values.

So in your case, I think you have three options.

  • Triggers
  • Stored procedures
  • Application code

In all those cases, you need to pay attention to permissions (probably revoking delete permissions) and to cases that can avoid your code. For example, the dbms command-line interface and GUI interface can be used to get around constraints in application code and, depending on permissions, in stored procedures.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 3
    Triggers are the only sensible way forward. Application code can't guarantee to be called and stored procedures (functions in PG) don't buy you anything over a simple trigger here. – Richard Huxton Nov 09 '12 at 13:59
  • Not sure how triggers will help me ensure the data integrity? If Employee.is_deleted = false then Company.is_deleted = false should also hold true. Can triggers guarantee that? – Björn Lindqvist Nov 09 '12 at 14:54
  • Triggers don't offer guarantees *in the same way* that declarative constraints do, but yes, you can write triggers in such a way that changing companies.is_deleted to true will execute a SQL statement that sets employees.is_deleted to true for that company. – Mike Sherrill 'Cat Recall' Nov 16 '12 at 06:06