Hello everybody

this question may be slightly out of topic, since the problem is not java-realated. But I've made very good experiences posting my questions here (and also reading the threads)......

I do have two tables "Dozent" (teacher) an "Abwesenheit" (absence). If I delete a teacher, I like to have deleted every tuple in the Abwesenheit-Table wich references this teacher. (referential integrity)

Thats why I am using InnoDB. My tables look like this:

CREATE TABLE IF NOT EXISTS Dozent( doz_id INT AUTO_INCREMENT, doz_kz VARCHAR(10) NOT NULL, name VARCHAR(30) NOT NULL, vorname VARCHAR(30) NOT NULL, PRIMARY KEY (doz_id), UNIQUE UC_doz_kz (doz_kz), UNIQUE idx_kz (doz_kz)) TYPE=INNODB;

CREATE TABLE IF NOT EXISTS Abwesenheit( ah_id INT AUTO_INCREMENT, dozent_doz_id INT NOT NULL, jahr CHAR(4) NOT NULL, grund TINYTEXT, stunden DOUBLE, INDEX idx_dozent_doz_id (dozent_doz_id), FOREIGN KEY (dozent_doz_id) REFERENCES Dozent (doz_id) ON DELETE CASCADE, PRIMARY KEY (ah_id)) TYPE=INNODB;

My opinion is, that mysql should delete the referencing tuples in the Abwesenheit-Table, but I get the ERROR 1217 instead. The manuanl about InnoDB on www.innodb.com/ibman.html says:

Starting from version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. If ON DELETE CASCADE is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row.

So far so good, it just doesen't work. I noticed that the version mentioned in the manual is 3.23.50, I couldn't find that version for download on www.mysql.com , thats why I am using 3.23.49-max-nt (on a Windows 2000 operating system ) .

Thanks for any help Marcus

Content Security by MailMarshal