[TYPO3-dev] IRRE - Intermediate tables for m:n relations - orphan records (was IRRE - SQL error: Unknown column)

[Boros Attila]

Hi,

Unfortunately I have found another problem with the same
configuration. The tx_chrsites_hotels and tx_chrsites_services tables
are linked via a third MN table named tx_chrsites_hotels_services.
Everything works fine until I delete a hotel record: the corresponding
data in tx_chrsites_hotels_services will not be deleted. And now I
have tx_chrsites_hotels_services.id_hotel pointing to a hotel which no
longer exists.

I have tried to switch to InnoDB and add foreign key constraints. I
have 2 FK constraints, one for each ID:

CREATE TABLE tx_chrsites_hotels_services (
        uid int(11) NOT NULL auto_increment,
        pid int(11) DEFAULT '0' NOT NULL,
        sys_language_uid int(11) DEFAULT '0' NOT NULL,
        l10n_parent int(11) DEFAULT '0' NOT NULL,
        l10n_diffsource mediumtext,
        id_hotel int(11) DEFAULT '0' NOT NULL,
        id_service int(11) DEFAULT '0' NOT NULL,

        PRIMARY KEY (uid),
        UNIQUE KEY idx_hotel_service (id_hotel, id_service),
        KEY parent (pid),
        CONSTRAINT fk_hotels1 FOREIGN KEY (id_hotel) REFERENCES tx_chrsites_hotels (uid) ON DELETE CASCADE,
        CONSTRAINT fk_services FOREIGN KEY (id_service) REFERENCES tx_chrsites_services (uid) ON DELETE CASCADE
) ENGINE = InnoDB;

The extension manager sees only the second constraint. If I copy this
into a mysql client it creates the table and the constraints just
fine. So I went on and created the constraints manually. Now I can't
add services to a hotel record, if I try I get this error message in a
red box on the top of the page:

    2: SQL error: '' ()

No idea what that could be, but it's definitely caused by the
constraints. What is the correct way to solve this problem? I wouldn't
like to keep orphan records in tx_chrsites_hotels_services.

--
Attila