mysql - Relational table design: prevent circular references -

i have problem not sure how solve correctly. current design consists of 2 tables. employee , employee_management. rdbms 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 employee(name) values(   'bob smith' ) insert employee(name) values(   'bill smith' )  insert employee_management(manager_id, employee_id) values(   1,2 ) insert employee_management(manager_id, employee_id) values(   2,1 ) 

selecting rows employee_management shows this:

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

the rows returned indicate bill smith manages bob smith, , bob smith manages bill smith believe circular reference. 2 people managing each other doesn't make sense. thought unique index prevent insertion of rows had existing combination of values, did not work. know can prevent happening @ application level, i'm not sure if appropriate thing do, has enforced @ application level, or there can prevent circular reference? managers should not have managers.

the main reason complexity poor support declaring these constraints dbms.

can state 2 types of table constraints decoratively:

• uniquely identifying attributes (keys)
• subset requirements referencing same table, in case subset requirement table constraint (foreign key same table).

implementing other types of table constraints requires develop procedural data integrity code. in practice, means you’ll have resort triggered procedural strategy or implementation in business layer of application.

following this , this post , may use trigger check if query below results more 0, rollback changes

select count(*) employee_management e1             exists (select * employee_management  e2                          e1.manager_id = e2.employee_id              ,             e1.employee_id = e2.manager_id ) 

as footnote may have foreign key employee employee showing manager of employee.