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.
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.