> -----Original Message----- > From: Alain Del Giust [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 09, 2002 12:48 PM > To: Christopher Thompson; [EMAIL PROTECTED] > Cc: Rance Hall > Subject: Re: mySQL and innoDB > > > I am just testing mySQL so I will be able to use it instead > of Oracle Sybase > ... > > I am not saying a PK can be NULL wich is impossible > But a FK can be NULL and I'd like to know how to do that with mySQL > > > A concrete example could be: > > table employee > column 1 : employee_id - primary key > column 2 : employee_name - name of the employee > > table hierarchy > column 1 : hierarchy_id - primary key > column 2 : hierarchy_employee_id_son - a foreign key referencing > employee.employee_id > column 3 : hierarchy_employee_id_father- a foreign key referencing > employee.employee_id > > > Now I want to use that: > > I have 6 employee > employeeA > employeeB > employeeC > employeeD > employeeE > employeeF > > I have 2 hierarchies X and Y > > For hierachie X > employeeA is the manager of employee C > employeeC is the manager of employee F > > For hierachie Y > employeeB is the manager of employee D and E > > So i have in my tables > > insert into table employee values ('A','employeeA'); > insert into table employee values ('B','employeeB'); > insert into table employee values ('C','employeeC'); > insert into table employee values ('D','employeeD'); > insert into table employee values ('E','employeeE'); > insert into table employee values ('F','employeeF'); > > insert into table hierarchy values ('X','A',NULL); > insert into table hierarchy values ('X','C','A'); > insert into table hierarchy values ('X','F','C'); > insert into table hierarchy values ('X','B',NULL); > insert into table hierarchy values ('X','D','B'); > insert into table hierarchy values ('X','E','B'); > > The employee who is at the top of the hierarchy as no manager ... > so for A and B in the hierarchy table the foreign key referencing > employee.employee_id is NULL > > Alain > I dont see where what you are doing is allowed, and I think Chris mentioned it in another note
But, Now I see where you are going, and I can think of Two different ideas 1. If the employee is a supervisor, then let him supervise himself/herself and populate the field accordingly 2. Create a supervisor field in the employee table, and programmatically enforce that an employees supervisor be in the select group of supervisor = yes employees --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
