How to insert row in table with foreign key to itself?

Question

I have table that has foreign key for itself. Column parentid is foreign key and it cannot be NULL.

if I doINSERT INTO mytable(name) VALUES('name'), so it says that can't insert NULL to parentid. BUT, what value I can set to it if no row was inserted yet?!

How I can write script that will add row to this table?

Thank you

Answer 1

Remove the NOT NULL constraint, as it is an inappropriate constraint. If you do not have a ParentId then the value is NULL and should be allowed. Creating a dummy row just to have a dummy parentid creates unnecessary dependencies.

Answer 2

A trick: Have a dummy row with a dummy key, say 99999. Insert with this as the FK, and then change the FK to its real value. And do it in a transaction.