Post Date: 01/27/2021
Last Updated: 01/27/2021
Today I ran into an issue where a user set a parent record to be itself in a poorly written form. No validation allowed a record to be the parent of itself.
For security and liability reasons, I will be using generic data but the concepts still apply.
The following would cause the record with a parent of themselves to not show up at all. This also lead to the parent record's children records not showing up either.
select employee_id, -- This column is used to provide indentation in an HTML select lpad ( employee_name, length ( employee_name ) + (level - 1) * 2, ' ' ) as display, employee_name, supervisor_id from employees start with coalesce(supervisor_id, -1) = -1 connect by prior employee_id = supervisor_id order siblings by lower (employee_name) asc
Let's call this table
This table should never have the same value in the
A proper table check constraint and form validation will prevent this from happening.
ALTER TABLE employees ADD CONSTRAINT employee_is_not_own_supervisor CHECK (employee_id <> supervisor_id) ENABLE;
This one is open to whatever system you are using. For my project I was using Vuelidate. Add a check in your code to verify that the fields tied to
supervisor_id are not the same value.
Test with the tools you normally use.
Hopefully this helps save you some time and frustration in the future. Thanks for reading!
I sell original music compositions, mainly for brass instruments over at Wayne Boka Music. Help support this blog by buying some music for yourself or the favorite musician in your life.