Relational table design: prevent circular references

Question

I have a problem that I am not sure how to solve correctly. My current design consists of two tables. employee and employee_management. My RDBMS is MySQL.

employee:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

employee_management:

CREATE TABLE `employee_management` (
  `manager_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
   UNIQUE INDEX `association` (`manager_id`,`employee_id`),
   FOREIGN KEY (`manager_id`)
   REFERENCES employee(id)
   ON UPDATE CASCADE ON DELETE RESTRICT,
   FOREIGN KEY  (`employee_id`)
   REFERENCES employee(id)
   ON UPDATE CASCADE ON DELETE RESTRICT
)

Test data:

INSERT INTO employee(name) VALUES(
  'Bob Smith'
)
INSERT INTO employee(name) VALUES(
  'Bill Smith'
)

INSERT INTO employee_management(manager_id, employee_id) VALUES(
  1,2
)
INSERT INTO employee_management(manager_id, employee_id) VALUES(
  2,1
)

Selecting the rows from employee_management shows this:

+------------+-------------+
| manager_id | employee_id |
+------------+-------------+
|          2 |           1 |
|          1 |           2 |
+------------+-------------+

The rows returned indicate that Bill Smith manages Bob Smith, and Bob Smith manages Bill Smith which I believe is a circular reference. Two people managing each other doesn't make sense. I thought that the UNIQUE INDEX would prevent the insertion of rows that had any existing combination of values, but that did not work. I know I can prevent this from happening at the application level, but I'm not sure if that is the appropriate thing to do, but is this something that has to be enforced at the application level, or is there something I can do to prevent a circular reference? Managers should not also have managers.

Can Bob Smith manage Dick Jones, who manages Bill Smith, who manages Bob Smith? — Strawberry, Feb 06 '14 at 08:53
In which case, consider switching to a nested set model - or, as you say, manage the business logic at the application level — Strawberry, Feb 06 '14 at 09:00
A unique index just makes sure that the values in the index column(s) are unique the tuple (1,2) is not the same as (2,1) so both are unique. But that is not a database design problem, its just bad data inserted. — Tarsis, Feb 06 '14 at 09:30
In nearly all other DBMS you *could* define a unique index that would prevent insertion of `(1,2)` and `(2,1)` - just not in MySQL — , Feb 08 '14 at 23:06

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

The main reason for this complexity is the poor support for declaring these constraints to the DBMS.

You can state only two types of table constraints decoratively:

Uniquely identifying attributes (keys)
Subset requirements referencing back to the same table, in which case a subset requirement is a table constraint (foreign key to the same table).

Implementing all other types of table constraints requires you to develop procedural data Integrity code. In practice, this means that you’ll often have to resort to the triggered procedural strategy or implementation in business layer of your application.

Following this and this post , you may use a trigger to check if the query below results more than 0, then rollback the changes

SELECT count(*) FROM employee_management e1
            WHERE EXISTS (SELECT * FROM employee_management  e2
            WHERE
             e1.manager_id = e2.employee_id
             AND
            e1.employee_id = e2.manager_id )

As a footnote you may have a foreign key from employee to employee showing the manager of the employee.

edited May 23 '17 at 10:33

Community

1
1

answered Feb 08 '14 at 18:20

Mohsen Heydari

7,256
4
31
46

The "poor support" is specific to MySQL. In many other DBMS (e.g. Postgres) you can easily define an index that would prevent `(1,2)` and `(2,1)` – Feb 08 '14 at 23:07
I could not find an index solution in Oracle too! – Mohsen Heydari Feb 09 '14 at 06:03
2

That will work in any DBMS that allows an index on an expression, not only Oracle. – Feb 09 '14 at 10:49
Dear @a_horse_with_no_name I am thinking about how it will be possible to prevent circular reference in more levels, if 1 is manager of 2 and 2 is manager of 3, is it possible to prevent 3 being the manager of 1 using some expression like what you have mentioned or a custom function needs to be implemented? – Mohsen Heydari Feb 17 '14 at 09:42

score 0 · Answer 2 · answered Aug 08 '17 at 21:06

I needed a trigger to do exactly this in SQL Server - if it helps someone solve the same problem, here's the trigger definition.

CREATE TRIGGER iu_erp_user
ON [dbo].[erp_user]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON

declare @erp_user_id int
declare @supervisor_supervisor_id int

select @erp_user_id = i.erp_user_id, @supervisor_supervisor_id = eu.supervisor_id
from inserted i
left join erp_user eu on i.supervisor_id = eu.erp_user_id

if @erp_user_id = @supervisor_supervisor_id
begin
    RAISERROR('Circular reference created.', 16, 1)
    ROLLBACK TRAN
end

END
GO

Relational table design: prevent circular references

2 Answers2