Mysql mapping table FK constraints

Question

Hi,

I'm experiencing some difficulties with foreign key constraints. I have three tables:

Features
    ID PK AUTO_INC
    Title VARCHAR
Subscriptions
    ID PK AUTO_INC
    Title VARCHAR
Subscriptionfeatures
    ID PK AUTO_INC
    feature_id INT (index)
    subscription_id INT (index)

When I have the following records

Features
    1    Testfeature
Subscriptions
    1    Testsubscription

I can insert the following record in Subscriptionfeatures when defining a FK constraint as follows

ALTER TABLE subscriptionfeatures ADD CONSTRAINT FK_feature FOREIGN KEY (feature_id) REFERENCES features(id);
Subscriptionfeatures
    x    1    1 => ok

But I can not insert the identical record when adding an ON DELETE CASCADE clause to the FK constraint, but i must admint i do not understand its reason for denial!

ALTER TABLE subscriptionfeatures ADD CONSTRAINT FK_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE;
Subscriptionfeatures
    x    1    1 => fails

Any help on this would be greatly appreciated!

Answer 1

This works for me:

CREATE TABLE Features (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        Title VARCHAR(100)
        ) Engine=InnoDB;
CREATE TABLE Subsriptions (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        Title VARCHAR(100)
        ) Engine=InnoDB;

CREATE TABLE Subscriptionfeatures (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        feature_id INT NOT NULL,
        subscription_id INT NOT NULL,
        KEY ix_subscriptionfeatures_feature (feature_id),
        KEY ix_subscriptionfeatures_subscription (subscription_id)
        )  Engine=InnoDB;
INSERT
INTO    Features
VALUES  (1, 'Testfeature');
INSERT
INTO    Subsriptions
VALUES  (1, 'Testsubscription');

ALTER TABLE subscriptionfeatures ADD CONSTRAINT FK_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE;

INSERT
INTO    Subscriptionfeatures (feature_id, subscription_id)
VALUES  (1, 1);

What is the error the MySQL gives?

Answer 2

Quassnoi, your code worked well for me too.

But it seems the three tables may not contain any data on the moment the ALTER query is executed to add the FK constraint. And there was the problem... .