CODEWITHSUNDEEP
mysqlforeign-keys
TeejMonster
TeejMonster

Reputation: 105

MYSQL Foreign Key enforcement needed

Consider the following SQL:

    CREATE DATABASE TEST01234;
    USE TEST01234;

    CREATE TABLE PARENT (
      PARENT_NAME varchar(255) PRIMARY KEY,
      PARENT_DESC varchar(255));

    CREATE TABLE CHILD (
      PARENT_NAME varchar(255),
      CHILD_NAME varchar(255),
      CHILD_DESC varchar(255),
      PRIMARY KEY (PARENT_NAME, CHILD_NAME),
      FOREIGN KEY (PARENT_NAME) REFERENCES PARENT(PARENT_NAME)
      ON UPDATE CASCADE
      ON DELETE CASCADE);

    INSERT INTO PARENT VALUES("PARENT A","Some Parent");

    INSERT INTO CHILD VALUES("PARENT A","CHILD A","Some Child");
    INSERT INTO CHILD VALUES("PARENT A","CHILD B","Some Child");
    INSERT INTO CHILD VALUES("PARENT A","CHILD C","Some Child");

    INSERT INTO CHILD VALUES("PARENT B","CHILD D","Non-present Parent");

    DELETE FROM PARENT WHERE PARENT_NAME = "PARENT A";

    SELECT * FROM CHILD;

Also,

    mysql> SHOW VARIABLES LIKE "%version%";
    +-------------------------+---------------------+
    | Variable_name           | Value               |
    +-------------------------+---------------------+
    | protocol_version        | 10                  |
    | version                 | 5.1.41-3ubuntu12.10 |
    | version_comment         | (Ubuntu)            |
    | version_compile_machine | i486                |
    | version_compile_os      | debian-linux-gnu    |
    +-------------------------+---------------------+
    5 rows in set (0.00 sec)

Two things are happening that I thought would be different.
* The first is that MySQL lets me insert values into CHILD that don't match anything in PARENT. I thought the foreign key restraint would stop that from happening.
* The second is that MySQL doesn't delete the PARENT A records from CHILD when PARENT A is deleted from PARENT. I thought that the cascade statement would make that happen.

I feel like I'm missing something obvious. Any suggestions?

Upvotes: 0

Views: 176

Answers (2)

Rolf van de Krol
Rolf van de Krol

Reputation: 444

Are you using InnoDB? MyISAM (the default engine in MySQL) does not support foreign keys, whereas InnoDB does.

Upvotes: 0

Marc B
Marc B

Reputation: 360572

You need to specify using the InnoDB engine for the tables. By default MySQL will use MyISAM tables, which understand foreign key specifications in "create table" queries, but ignore the keys themselves.

If you do a 'show create table PARENT', you'll most likely see ENGINE=MyISAM.

Upvotes: 1

Related Questions