foreign key to the *same* table?

From: Marco <abc_at_def.hij>
Date: Wed, 10 Sep 2003 00:05:46 +0200
Message-ID: <bjlit9$ish$1_at_lacerta.tiscalinet.it>

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 Received on Wed Sep 10 2003 - 00:05:46 CEST