Question 232889 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to protect a single database row / Model instance?

We have customers, and Customer objects. One of these, which I was creating with a migration, was Customer( account='INTERNAL' ...) as a placeholder for when we manufacture stuff for internal consumption.

Customers come and go, so the objects need to be deletable. But this single row must not be deleted.

What is the best way to stop it getting accidentally deleted? I can replace all instances of Customer.objects.get(account='INTERNAL') with get_or_create so it is self-resurrecting, but it feels like storing up trouble for the future if there are ForeignKey references to it and then it gets deleted. (It can’t be Models.PROTECT, because other customers can be deleted).

Tagged [postgresql], because that’s what we use. I suspect there might be a way to use database functionality to do what I want, but I’d appreciate it if the answer showed how to write doing that as a Django migration (if that’s possible). I know more about Django than SQL and Postgres extensions.

[update] As an imperfect fix, I have subclassed the Customer.delete method. It now throws an error if the account name is INTERNAL. But this isn’t bullet-proof. This may be good enough, but it doesn’t feel like a right thing.

Tags:

3

Answers


  1. 0

    you can add an extra property for eg: customer_type and assign value "internal" for row which must not be deleted and some other value for other customers. When deleting, you can check this column.

    Login or Signup to reply.
  2. 0

    You can use on_delete=models.PROTECT to created related record that will prevent deletion of the Customer until it exists

    Create special model for this case

    class CustomerDeleteProtection(models.Model):
    customer = models.ForeignKey(
        Customer, related_name='+',
        on_delete=models.PROTECT)

    When you will try to remove related customer it will raise DB error

    Login or Signup to reply.
  3. 0

    I would suggest to cover this requirement by supporting authorization (and eventually authentication). And this can be done at a DBMS level (PostgreSQL) or API level.

    Example: in your API you design/develop two following methods:

    • a delete-external-customers method and a wide audience is entitled to invoke this method
    • a delete-internal-customer(s) method and only a limited (and authenticated) audience is entitle to invoke this method

    At a DBMS level, I’m inclined to believe you can establish this as well with grants and/or views.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search