Search code examples
databaserequired

How to manage required data for a table


I have a table that there are 3 required records that must be present in order for the application that uses this table to function correctly. For example, if this is a linked list tree table, in my case, there are three top level groups that must be present. When I start using this table all future groups must be always under a group and no other top-level group can be created.

Group TL1
   Group A
       Group AA

Group TL2
   Group B
   Group B1
      Group B1B

Group TL3
   Group C

Note Group TL1, TL2, and TL3 must always be present, or else the data integrity is broken for the application's requirement.

What is the best way to insert/guard the required top level groups?

One idea I have is to have required data inserted upon table creation and have a function that checks for the presence of the required data. However, I also don't want to always check for their existence as it seems excessive and in-efficient.

Your replies are greatly appreciated.


Solution

  • Depending on the database, you can try things like:

    1) Having a foreign key constraint that prevents the update/deletion if it is broken - if row A references row Important by foreign key, then you cannot delete row Important as the database will check the constraint and prevent it.

    2) Having a trigger that runs before deletes/updates/inserts (as needed), ensures that the important rows are present and prevents the action (or inserts) if it would violate this.

    I would look into whatever database flavour you use's options.