Insert self referencing entry into SQL Server
Insert self referencing entry into SQL server


For the general question as per the title, you can add a direct circular reference in a simple insert such as 

INSERT node 
       (id , name  , parent_id) 
VALUES (123, 'Test', 123      )


because the constraint is enforced considering all the new data: as long as the value the FX references exists once the statement is complete all is well. This is the same as inserting several values in a linked list:

INSERT node 
       (id , name   , parent_id) 
VALUES (101, 'Test1', 100      )
     , (102, 'Test2', 101      )
     , (103, 'Test3', 102      )


or an indirect circular reference:

INSERT node 
       (id , name   , parent_id) 
VALUES (201, 'Test5', 203      )
     , (202, 'Test6', 201      )
     , (203, 'Test7', 202      )



  So the value in the created_by column should be the auto-incremented value of the id column


This poses a problem because you don't know what the generated is before you insert, in fact there is no reliable way of knowing. For inserting individual rows you can use SCOPE_IDENTITY() to immediately update the new row:

INSERT node 
       (name  ) 
VALUES ('Test')
-- and now make the circular reference
UPDATE node 
SET    parent_id = SCOPE_IDENTITY()  
WHERE  id        = SCOPE_IDENTITY()


In the above example if parent_id is a required column (declared NOT NULL with no DEFAULT) then you'll need to provide a dummy temporary valid value instead of leaving it out of the initial INSERT statement, like so:

INSERT node 
       (name  , parent_id) 
VALUES ('Test', 0        )
-- and now make the circular reference
UPDATE node 
SET    parent_id = SCOPE_IDENTITY()  
WHERE  id        = SCOPE_IDENTITY()


For dealing with inserts of multiple rows (unlikely in the circumstance you describe but common elsewhere) you can use the OUTPUT clause to read the IDs created for each row for further reference. I'll not go over that here as it is overkill for the current question, see https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql for more detail.


  So the value in the created_by column should be the auto-incremented value of the id column


It turns out that this can be done with a SEQUENCE in SQL Server if you have 2012 or above, which is a neater solution than the multi-statement options above, see SQLing4ever's answer for a worked example. Before 2012 this feature was not available, so you'll need to fall back to the methods in this answer if you need to support older instances of SQL Server.