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
----- Original Message -----
From: "Christopher Thompson" <[EMAIL PROTECTED]>
To: "Alain Del Giust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, April 09, 2002 7:04 PM
Subject: Re: mySQL and innoDB
> On Tuesday 09 April 2002 11:07 am, Alain Del Giust wrote:
> > "if you have a foreign key constraint, that field must be one of the
values
> > of the referenced column and that column, being an index, is never NULL"
> >
> > OK so my understanding is that it is impossible with mySQL to do such
> > things wich are basic with at least:
> > Oracle
> > Sybase Adaptive Server Enterprise
> > Sybase Adaptive Server Anywhere
>
> What are you saying? If you are saying these databases allow a primary
key
> column (or a unique key column) to contain a NULL, they are violating
SQL92.
> By extension, any column that is a foreign key can also not be NULL.
>
> Perhaps you are saying something else. But if you are saying this, you
can
> hardly take MySQL to task for properly supporting SQL92 (at least, in this
> instance).
>
---------------------------------------------------------------------
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
