Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
No idea really what PHPMyAdmin might be doing. The definitions seem ok. Perhaps it only needs "refresh"? Or disconnect and reconnect? –  ypercube Apr 23 '12 at 14:11
    
You can try a SHOW CREATE TABLE OrderContains in the "SQL" tab to be sure that the table was created as above. –  ypercube Apr 23 '12 at 14:13
    
I used "SHOW CREATE TABLE" and added the result to the end of the question. Can't find and difference from what I originally wrote. –  Olof Bjerke Apr 23 '12 at 14:34
    
The FKs seem to have been defined correctly. So, the issue is that deletes/updates are not cascaded? Or that PHPMyAdmin does not "show" the FKs ? –  ypercube Apr 23 '12 at 14:37
    
phpMyadmin does not show the FK's or the UPDATE/DELETE rules for OrderContains in the relational view. Here is a screenshot of phpMyadmin which might help dl.dropbox.com/u/601426/phpmyadmin.png. As the picture shows you, Orders.orderNr is not visible in that list. –  Olof Bjerke Apr 23 '12 at 14:51

1 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.

share|improve this answer
    
Thanks for that explanation. –  Olof Bjerke Apr 24 '12 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.