Issue: CASCADE for mysql5.5 not work.
Author: jander
State: closed
Created: 2011-12-14 03:23:51

in my model,  cascade for mysql not work, peewee generate create table sql:

```
('CREATE TABLE space (id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(255));', None)
('CREATE UNIQUE INDEX space_id ON space(id);', None)
('CREATE TABLE page (id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, space_id INTEGER REFERENCES space (id) ON DELETE CASCADE, name VARCHAR(255));', None)
('CREATE  INDEX page_space_id ON page(space_id);', None)
('CREATE UNIQUE INDEX page_id ON page(id);', None)
```

it looks like  lack  mysql **FOREIGN KEY**  statements:

```
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
```


Yeah, you're totally right -- looks like the syntax for creating a foreign key in mysql is a bit different.  Django has a handy way of generating SQL so I created a simple model with a foreign key and generated some output for the 3 backends supported by peewee... the MySQL backend has an additional step:

MySQL:

```
CREATE TABLE `xxx_a` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `a_field` varchar(255) NOT NULL
)
;
CREATE TABLE `xxx_b` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `b_field` varchar(255) NOT NULL,
    `a_id` integer NOT NULL
)
;
ALTER TABLE `xxx_b` ADD CONSTRAINT `a_id_refs_id_78d26df2` FOREIGN KEY (`a_id`) REFERENCES `xxx_a` (`id`);
CREATE INDEX `xxx_b_776cf5a8` ON `xxx_b` (`a_id`);
```

Sqlite:

```
CREATE TABLE "xxx_a" (
    "id" integer NOT NULL PRIMARY KEY,
    "a_field" varchar(255) NOT NULL
)
;
CREATE TABLE "xxx_b" (
    "id" integer NOT NULL PRIMARY KEY,
    "b_field" varchar(255) NOT NULL,
    "a_id" integer NOT NULL REFERENCES "xxx_a" ("id")
)
;
CREATE INDEX "xxx_b_776cf5a8" ON "xxx_b" ("a_id");
```

Postgresql:

```
CREATE TABLE "xxx_a" (
    "id" serial NOT NULL PRIMARY KEY,
    "a_field" varchar(255) NOT NULL
)
;
CREATE TABLE "xxx_b" (
    "id" serial NOT NULL PRIMARY KEY,
    "b_field" varchar(255) NOT NULL,
    "a_id" integer NOT NULL REFERENCES "xxx_a" ("id") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE INDEX "xxx_b_a_id" ON "xxx_b" ("a_id");
```


it is simple to solve it.

**1.  in class Database, add a method: create_foreign_key.**

``` python
class Database(object):

    def create_foreign_key(self, model_class, to_class, field_name, cascade=False):
        # just call create_index
        self.create_index(model_class, field_name)
```

**2.  in class MyDatabase, add a method: create_foreign_key.**

``` python
class MySQLDatabase(Database):

    def create_foreign_key(self, model_class, to_class, field_name, cascade=False):
        framing = '''ALTER TABLE %(model)s ADD CONSTRAINT fk_%(model)s_%(to)s_%(field)s
FOREIGN KEY (%(field)s ) REFERENCES %(to)s(id) %(cascade)s;'''

        if field_name not in model_class._meta.fields:
            raise AttributeError(
                'Field %s not on model %s' % (field_name, model_class)
            )

        query = framing % {
            'model': model_class._meta.db_table,
            'field': field_name,
            'to': to_class._meta.db_table,
            'cascade': ' ON DELETE CASCADE' if cascade else '',
        }

        self.execute(query)
```

**3.  in class Model, change the method: create_table.**

``` python
class Model(object):
    @classmethod
    def create_table(cls, fail_silently=False):
        if fail_silently and cls.table_exists():
            return

        cls._meta.database.create_table(cls)

        for field_name, field_obj in cls._meta.fields.items():
            if isinstance(field_obj, PrimaryKeyField):
                cls._meta.database.create_index(cls, field_obj.name, True)
            elif isinstance(field_obj, ForeignKeyField):
                # call create_foreign_key, but not create_index
                cls._meta.database.create_foreign_key(cls, field_obj.to, field_obj.name, field_obj.cascade)
            elif field_obj.db_index or field_obj.unique:
                cls._meta.database.create_index(cls, field_obj.name, field_obj.unique)
```


I've adapted your changes here dccb160581d1f60405e9b15e6b892d8f5d149eeb

The cascade on delete test is now passing w/mysql as well!  Thanks for bringing this up and for the implementation!