Problem with self referencing Foreign key field and mysql InnoDB

[limas]

I were using Mysql MyISAM. But I want to enable transaction.
So i shifted to InnoDB.
Actually I have one model as below (*designed somebody i can't
change).

class Folder(models.Model):
folder_id=models.AutoField(primary_key=True)
user_name=models.ForeignKey(User)
folder_name=models.CharField(max_length=80)
parent_folder=models.ForeignKey('self')

At some point i want to insert in this table as below:
Folder(user_name_id=1,folder_name="s",parent_folder_id=0)

But it raises one error like this.

IntegrityError: (1452, 'Cannot add or update a child row: a foreign
key constraint fails (`myproject/folder_folder`, CONSTRAINT
`parent_folder_id_refs_folder_id_12515019` FOREIGN KEY
(`parent_folder_id`) REFERENCES `folder_folder` (`folder_id`))')

But with MyISAM there was no problem like this.......

Anyways i need Transaction functionality for other tables in the
database.
Is there any solution for this problem. Please Help me.

Thanks
Lima

[Daniel Roseman]

The other difference between InnoDB and MyISAM is that InnoDB supports
- and enforces - foreign key constraints. '0' does not refer to an
existing folder_id, so the constraint fails. There isn't any way
around that with the way you've set up your models currently: you need
to change the parent_folder field so that null=True.

You say you can't change the db structure, but obviously you've
already made some changes to it to move to InnoDB. I recommend you add
null=True to the Django field definition, then run this SQL on the
database:
ALTER TABLE appname_folder MODIFY COLUMN parent_folder_id INTEGER
NULL;

Now you can define your folder without setting a parent_folder:
Folder(user_name_id=1,folder_name="s")

--
DR.

[Tom Evans]

Your model code says "I have a foreign key to myself, and it is always
required", which might make a tree structure hard to derive! Change the
'parent_folder' member definition to look like so:
parent_folder = models.ForeignKey('self', null=True, blank=True)

Also, instead of setting parent_folder_id to be 0 (which surely does not
refer to a valid Folder model instance?), you should either not set it
at all or set it to None.

The reason it worked before, is that MyISAM has no referential
integrity, where as InnoDB does, so when you try to insert that row, it
now refuses because there is no such row in the folder_folder table with
the id 0. MyISAM would never check that, so your code worked.

Cheers

Tom