Why do my ON UPDATE/ON DELETE rules disappear?

Question

I'm trying to connect a couple of Mysql tables with foreign keys and ON UPDATE/DELETE rules. My rules are working on all tables except the last one (OrderContains). Why aren't my rules working?

I am using phpMyadmin to run my hand written queries and I can see in the relational view (part of phpMyadmin) of the OrderContains table that there are no connections. I can't even find the Orders.orderNr in the list of possible connections.

Any suggestions?

Here are my table definitions:

CREATE TABLE Users   (
    userNr          INT,
    password        VARCHAR(50),
    language        CHAR(3),
    name            VARCHAR(32),
    email           VARCHAR(32),
    phone           VARCHAR(32),
    security_level  INT,
    address         VARCHAR(32),
    comment         TEXT,

    PRIMARY KEY     (userNr)
)  ENGINE=INNODB;

CREATE TABLE Customers   (
    userNr          INT,

    FOREIGN KEY (userNr)    REFERENCES  Users(userNr)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    PRIMARY KEY (userNr)
) ENGINE=INNODB;

CREATE TABLE Items   (
    itemNr          VARCHAR(32),
    revision        VARCHAR(32),
    description     TEXT,
    weight          FLOAT,
    added           DATE,       

    PRIMARY KEY(itemNr, revision)
) ENGINE=INNODB;

CREATE TABLE Orders  (
    customer        INT,
    orderNr         INT,
    title           VARCHAR(32),
    added           DATE,
    status          CHAR(1),
    notes           TEXT,

    PRIMARY KEY     (customer, orderNr),
    FOREIGN KEY     (customer)      REFERENCES  Customers(userNr)
    ON UPDATE CASCADE
    ON DELETE CASCADE

) ENGINE=INNODB;


CREATE TABLE OrderContains   (
    customer        INT,
    orderNr         INT,
    itemNr          VARCHAR(32),
    revision        VARCHAR(32),
    quantity        INT,

    PRIMARY KEY     (customer, orderNr, itemNr, revision),
    FOREIGN KEY     (customer, orderNr) REFERENCES  Orders(customer, orderNr)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    FOREIGN KEY     (itemNr, revision)  REFERENCES  Items(itemNr, revision)
    ON UPDATE CASCADE
    ON DELETE CASCADE

) ENGINE=INNODB;

I made the following query in phpMyAdmin SHOW CREATE TABLE OrderContains

and I got this result:

CREATE TABLE `OrderContains` (
 `customer` int(11) NOT NULL DEFAULT '0',
 `orderNr` int(11) NOT NULL DEFAULT '0',
 `itemNr` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `revision` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 `quantity` int(11) DEFAULT NULL,
 PRIMARY KEY (`customer`,`orderNr`,`itemNr`,`revision`),
 KEY `itemNr` (`itemNr`,`revision`),
 CONSTRAINT `OrderContains_ibfk_1` FOREIGN KEY (`customer`, `orderNr`)     REFERENCES `Orders` (`customer`, `orderNr`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `OrderContains_ibfk_2` FOREIGN KEY (`itemNr`, `revision`)     REFERENCES `Items` (`itemNr`, `revision`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I've also tried restarting the server.

Answer 1

DISCLAIMER : Not a user of phpMyAdmin

When visually comparing the table design and the screenshot, I noticed something interesting: It seems that phpMyAdmin is not designed to graphically represent a compound constraint (a constraint with more than one column). How can you tell ???

• The Users table has a PRIMARY KEY on the single column userNr. A primary key is a constraint. (Just run select count(1),CONSTRAINT_TYPE from information_schema.table_constraints group by CONSTRAINT_TYPE; and will see PRIMARY KEY registered as a constraint).
• The Customers table has the sinlge column userNr as PRIMARY KEY also, thus it is a constraint.
• The Orders table has a compound PRIMARY KEY, but the screenshoot only shows the first column of the PRIMARY KEY.
• The Items table has a compound PRIMARY KEY, but the screenshoot only shows the first column of the PRIMARY KEY.
• The OrderContains table has 3 constraints, 1 PRIMARY KEY and 2 Foreign Key. Notice that the two foreign key constraints are defined as having two columns each but the only the first column of each constraint is displayed in the screenshoot.

You can use information_schema.KEY_COLUMN_USAGE

mysql> desc KEY_COLUMN_USAGE;
+-------------------------------+--------------+------+-----+---------+-------+
| Field                         | Type         | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG            | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME               | varchar(64)  | NO   |     |         |       |
| TABLE_CATALOG                 | varchar(512) | NO   |     |         |       |
| TABLE_SCHEMA                  | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                    | varchar(64)  | NO   |     |         |       |
| COLUMN_NAME                   | varchar(64)  | NO   |     |         |       |
| ORDINAL_POSITION              | bigint(10)   | NO   |     | 0       |       |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |     | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |     | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |     | NULL    |       |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

mysql>

Notice that the information for constraints and all participating columns can be located here. Since you can see the constraints you defined using the mysql client, rest assured that you are not going crazy. It just seem to me phpMyAdmin is not coded correctly for multicolumn constraints.

Therefore, phpMyAdmin cannot properly see the UPDATE and DELETE CASCADE rules for a compound constraint because its source code is most likely using the first column only as the reference point to the information_schema database.