MySQL insert with undefined foreign key

Question

I have some table with such schema (simplified):

CREATE TABLE folders(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  parent INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  INDEX FK_folders_folders_id (parent),
  CONSTRAINT FK_folders_folders_id FOREIGN KEY (parent)
  REFERENCES folders (id) ON DELETE CASCADE ON UPDATE CASCADE
)

1 folder can have many subfolders and can belongs to one another folder. If it's root folder then parent will contain it's own ID.

The problem is: how can I create root folder? ID is auto_increment and I can get it only after inserting a row but I cant insert row while parent is not defined. Recursion...

Why is parent it's own ID when the folder is in root? Wouldn't it be better to insert a zero (0) into it? — thedom
– thedom, Commented Feb 5, 2011 at 16:45
Impossible cause there's foreign key to ID that is NOT NULL (PRIMARY KEY) — Chvanikoff
– Chvanikoff, Commented Feb 5, 2011 at 16:45
off topic : read this : dev.mysql.com/tech-resources/articles/hierarchical-data.html and consider using the nested set model if you need to, it could spare you some headaches — greg0ire
– greg0ire, Commented Feb 5, 2011 at 16:52

Oleksandr Kruk · Accepted Answer · 2011-02-05 17:16:58Z

1

You can remove the NOT NULL attribute from the parent field, this way you can have your root. Of course, in this case you have to garantee the folder tree cosistency in your code and not through database. MySQL reference manual does not advise users to do this:

You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys. http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

but it's always up to you.

answered Feb 5, 2011 at 17:16

Oleksandr Kruk

1538 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL insert with undefined foreign key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related