Sql check logical errors in 2 columns

Question

Assuming I have an employee table with only two columns:

• employee_id
• manager_id

All employees added to this table will have an accompanying manager_id , which is actually an employee_id that already exists (with the exception of one, the CEO probably doesn't have a manager, but that doesn't matter).

If A is the manager of B , how do we apply the check so that the manager can accept any value of BUT B , which leads to a violation of the business rule?

Answer 1

I would say that the best way is to create TRIGGER on the insert in the table, which will simply verify that manager_id NOT IN (SELECT employee_id from employee where manager_id = %insertid%) .

Answer 2

The problem goes deeper than this, you want to avoid any loops on your chart by using the tree efficiently.

I think you better do this at the application level.

UPDATE: But if you prefer to do this with a trigger, see Common Table Expressions (CTE) . You can create a recursive query in a trigger that checks for loops:

 create trigger prevent_management_cycles on employee instead of update as declare @found_rows int ;with cycle_detector (employee_id) as ( select employee_id from inserted union all select employee.employee_id from employee join cycle_detector on employee.manager_id = cycle_detector.employee_id ) select @found_rows = count(*) from cycle_detector join inserted on inserted.manager_id = cycle_detector.employee_id if @found_rows > 0 raiserror('cycle detected!', 1, 1) else -- carry on original update update employee set employee.manager_id = inserted.manager_id -- other columns... from employee join inserted on employee.employee_id = inserted.employee_id 

Note : it is assumed that employee_id is the primary key, and manager_id is a foreign key pointing to employee.employee_id .

Answer 3

Half of the answer is a foreign key: manager_id refers to the employee (employee_id)

The other half is a test constraint, manager_id <> employee_id