MySQL Forums :: Foreign Keys (Referential Integrity) :: How To Implement Self-Join with Referential Integrity?

Just allow managerID to be NULL. You said you are trying to avoid NULLs, but what is better? A '0', invalid value for manager, or a NULL 'do not have one' value?


CREATE TABLE employees (
employeeID int(10) NOT NULL auto_increment,
employeeName varchar(50) NOT NULL,
isManager tinyint(1) NOT NULL DEFAULT 0,
managerID int(10),
PRIMARY KEY (employeeID),
FOREIGN KEY (managerID) REFERENCES employees (employeeID) ON DELETE RESTRICT
) ENGINE=InnoDB;