Alberto, you are probably using an old version of InnoDB.
http://www.innodb.com/ibman.html#InnoDB_foreign_keys " A deviation from SQL standards: if ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update a table for which there already is an update operation in the stack of cascaded operations, it acts like RESTRICT. In plain English this means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, works starting from 4.0.13. A self-referential ON DELETE CASCADE has always worked. " [EMAIL PROTECTED]:~/mysql-4.0/client> mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.15-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 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_DIDACTIC_UNIT_LENDER ) REFERENCES DIDACTIC_UNITS (ID) ON -> DELETE SET NULL -> -> ) TYPE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> insert into DIDACTIC_UNITS(ID, ID_DIDACTIC_UNIT_LENDER) values (15, NULL) ; Query OK, 1 row affected (0.00 sec) mysql> insert into DIDACTIC_UNITS(ID, ID_DIDACTIC_UNIT_LENDER) values (25, 15); Query OK, 1 row affected (0.00 sec) mysql> delete from DIDACTIC_UNITS where ID = 15; Query OK, 1 row affected (0.00 sec) mysql> select * from DIDACTIC_UNITS; +----+-----------+--------------+---------------------+--------------------- ---- +------+-------------+-----------------+------+------------+ | ID | ID_COURSE | ID_PROFESSOR | ID_PERIOD_OF_LESSON | ID_DIDACTIC_UNIT_LENDER | NAME | COURSE_YEAR | HOURS_OF_LESSON | CFU | OBLIGATORY | +----+-----------+--------------+---------------------+--------------------- ---- +------+-------------+-----------------+------+------------+ | 25 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | +----+-----------+--------------+---------------------+--------------------- ---- +------+-------------+-----------------+------+------------+ 1 row in set (0.00 sec) mysql> Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: ""Alberto"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 10:07 PM Subject: Fw: [SQL] foreign key from a table to the *same* table > 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 > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
