Impose limits on number of records stored in a table



  • Assume we have a table with following schema:

    favorites{
        UserID
        CategoryID
    }
    

    How can I enforce a policy which ensures each users has at least 3 categories and at most 5 categories. Is it possible to enforce this policy at table creation time at all? or I should use triggers?

    NOTE:
    I know I can enforce maximum policy by adding an additional attribute as below:

    favroites{
       UserID
       CategoryID
       Count(check Count < 5)
       primary key(UserID, Count)
    }
    

    But I don't have any idea how to implement minimum policy when defining table.



  • I know I can enforce maximum policy by

    That example seems wrong. Each row in that table is a relationship between a user and a category, it is a junction table (sometimes called a relationship table). The count is not a property of the relationship but of the user so that should be in the user table.

    What if someone deletes the row where count=2? You'd need to enforce the update of those with count>2, perhaps with a trigger, but this would be problematically complex compared to using a trigger or computed column to update a count in the user table.

    Back to the original question:

    How can I enforce a policy which ensures each users has at least 3 categories and at most 5 categories.

    If you keep the count in the User table then you should be able to define a constraint something like category_count BETWEEN 3 AND 5 OR category_count = 0 - the extra check for zero categories being there to deal with new User rows that can not yet have categories listed in the junction table (because listing the relationships there before the user row exists would be a foreign key violation).

    If you update the count via trigger rather than computed column then I'd instead not have the count but just do the calculation in the trigger and raise an error if the value is not correct.

    In either case you still have to address in your application making sure that a user gets the minimum set when their row in the user table is created, you are not going to be able to enforce that in the database. Also you need to make sure the first 3 (or whatever your minimum is) are all created in the same statement or the checks will fail when the first on is added (and it must be the same statement not just the same transaction, triggers, computed columns, and check constrains will be run per statement not per transaction).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2