Hi to all...i've a problem...would anyone help me?

I've a table called COURSES, where there are some universitary courses.
I've a table called DIDACTIC_UNITS, where there are some subjects with some
info like the professor, the course year, etc.

The problem is that some didactic units can be lender to other didactic
units. For example, if you are a student of the course of 'Medicine', you
will see the lessons of "Internet and the www" with the student of the
course of 'Information technology'.
In this example, the didactic unit "Internet and the www" of the course
'Information technology' is the didactic unit lender for the didactic unit
'Information technology' of the course 'Medicine'.

So, i need a sort of 'foreign key' from the table DIDACTIC_UNITS to the
same table DIDACTIC_UNITS....with a field called DIDACTIC_UNIT_LENDER that
point to the record of the didactic unit lender, or null if the didactic
unit don't need another didactic unit.

Below some of the table metadata....please help me...MySQL let me create
the
table and insert records, but problem begins with the clause ON DELETE SET
NULL.... if i try to delete a record of a didactic unit that is a didactic
unit lender for another didactic unit, the deletion falied....

So i think this isn't a good solutions...but how i can solve this problem?

CREATE TABLE DIDACTIC_UNITS (

ID INTEGER NOT NULL AUTO_INCREMENT,
ID_COURSE INTEGER NOT NULL,
ID_PROFESSOR INTEGER,
ID_PERIOD_OF_LESSON INTEGER,
ID_DIDACTIC_UNIT_LENDER INTEGER,
NAME CHAR(50),
COURSE_YEAR INTEGER UNSIGNED,
HOURS_OF_LESSON INTEGER UNSIGNED,
CFU INTEGER UNSIGNED,
OBLIGATORY BOOL DEFAULT 1,

PRIMARY KEY(ID),
INDEX didactic_units_id_course (ID_COURSE),
INDEX didactic_units_id_professor (ID_PROFESSOR),
INDEX didactic_units_id_period_of_lesson (ID_PERIOD_OF_LESSON),
INDEX didactic_units_id_didactic_unit_lender (ID_DIDACTIC_UNIT_LENDER),

FOREIGN KEY(ID_COURSE) REFERENCES COURSES(ID) ON DELETE CASCADE,
FOREIGN KEY(ID_PROFESSOR) REFERENCES USERS(ID) ON DELETE SET NULL,
FOREIGN KEY(ID_PERIOD_OF_LESSON ) REFERENCES PERIODS_OF_LESSON (ID) ON
DELETE SET NULL,
FOREIGN KEY(ID_DIDACTIC_UNIT_LENDER ) REFERENCES DIDACTIC_UNITS (ID) ON
DELETE SET NULL

) TYPE = InnoDB;

Please help me....thanks very much!
Marco

Look up the nested sets model for hierarchies and use that to model
the courses instead of a self-reference. Then create a table to bring
the professor, the course, etc. into a course catalog table.

Would you like to make a little example?
I'm not sure to understand....

Thanks very much

On Wed, 10 Sep 2003 00:05:46 +0200, Marco wrote:

First, beware of MySQL -- it is not ISO SQL compliant, it has
lotsa arbitrary restrictions. PostgreSQL is just as cheap, and much
better.

Second, think again... any solution involving NULLs is suspect.
A nice, reationally sane solution to the hierarchy problem has been
proposed by Fabian Pascal in his latest book, and it is a pearl of
simplicity: simply a separate binary relation with the relationship
between hierarchy nodes; for it, suffices two foreign keys to the main
node relation.


--
_ Leandro Guimarães Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Answer to the list, not to me directly! +55 (11) 5686 2219
/ \ Rate this if helpful: http://svcs.affero.net/rm.php?r=leandro