One (optional) to Many [recursive]
Diagram 1o2M-rec.png
How to read One employee may manage many employees. Each employee may be managed by a manager.
Relevant relations The many side of this relation can be either optional or mandatory.

The table structures are as follows:

employee(__id__, name, <e_other>)
manage(__emp_id__*, mgr_id*)

The SQL create statement is as follows:

create table employee
  (id type primary key,
   name type,
create table manage
  (emp_id type primary key,
   mgr_id type not null,
   foreign key (mgr_id) references employee,
   foreign key (emp_id) references employee);

The reader should note that mgr_id cannot be the primary key of manage because many employees can have the same manager.

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License