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://learn.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.