3

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.

1

2 Answers 2

5

It looks like you are using an Identity for your Primary Key. If you need flexibility with your primary key I would recommend using a sequence. it would look something like this.

CREATE SEQUENCE SQ_temp AS INT INCREMENT BY 1 START WITH 1

CREATE TABLE Users 
( ID INT PRIMARY KEY DEFAULT NEXT VALUE FOR SQ_temp
, UserName VARCHAR(30)
, createdBy INT REFERENCES Users (ID)
)


INSERT INTO dbo.Users
(
    ID
    , UserName
    , createdBy
)
VALUES
(NEXT VALUE FOR SQ_temp,'User1', NEXT VALUE FOR SQ_temp)
, (NEXT VALUE FOR SQ_temp,'User2', NEXT VALUE FOR SQ_temp)
, (NEXT VALUE FOR SQ_temp,'User3', NEXT VALUE FOR SQ_temp)


SELECT *
FROM dbo.Users AS u
3
  • This is nice to know but how are you proposing to use sequences to address the OP's problem?
    – Andriy M
    Dec 19, 2018 at 19:15
  • Andriy M, I guess I'm a little confused about your question. Are you asking me how sequences can help the original person's question? To my understanding the Original Person's question was "How do I self reference the primary key of the same row?" (You haven't inserted it, and sometimes users get created by the same user). My answer is that you can use sequences to specify the SAME sequence ID for the same row. You can specify a 'created_by' integer OR tell the insert statement to use the same sequence ID for the user_id and the 'created_by' column Dec 19, 2018 at 21:02
  • Great idea. Just one more question: Since I have to use this on an existing table with an exiting id number, is it possible to start the sequence on a dynamic number? The START SEQUENCE command only takes a const. Any way to work around that? Since I can't be completely sure where my id will be when we'll run the update scripts. Update: I think I just found the solution: stackoverflow.com/questions/26913634/…
    – Remy
    Dec 20, 2018 at 16:20
6

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.

1
  • We are using AzureSQL, so I went with the answer from SQLing4ever. But thanks a lot anyway. This was very helpful.
    – Remy
    Dec 21, 2018 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.