Insert self referencing entry into SQL Server

I have a Person table that has a created_by column that references the primary id of the table itself. So, it could be an employee that adds another employee to the database. It works fine.

But People can also add themselves (signup). So the value in the created_by column should be the auto-incremented value of the id column. But that value is obviously not available until after the insert.

So I could either (a) make the reference not to check the values, (b) add a default value in the beginning or (c) make the column nullable. All options seem bad to me.

The MySQL's dialect has this:

SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO EMPLOYEE VALUES('12345','67890'),('67890','12345');
SET FOREIGN_KEY_CHECKS = 1;

...but I could not find something similar for SQL Server's T-SQL.

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.

StackExchange Database Administrators Q#225274, answer score: 6