Insert self referencing entry into SQL Server

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP












2














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.










share|improve this question























  • Perhaps an AFTER INSERT trigger would do that (UPDATE the column with the PK/identity value when it is null)? Like this: stackoverflow.com/questions/13249936/…
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 15:55
















2














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.










share|improve this question























  • Perhaps an AFTER INSERT trigger would do that (UPDATE the column with the PK/identity value when it is null)? Like this: stackoverflow.com/questions/13249936/…
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 15:55














2












2








2







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.










share|improve this question















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.







sql-server database-design t-sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 18 at 20:44









MDCCL

6,68731744




6,68731744










asked Dec 18 at 15:14









Remy

1184




1184











  • Perhaps an AFTER INSERT trigger would do that (UPDATE the column with the PK/identity value when it is null)? Like this: stackoverflow.com/questions/13249936/…
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 15:55

















  • Perhaps an AFTER INSERT trigger would do that (UPDATE the column with the PK/identity value when it is null)? Like this: stackoverflow.com/questions/13249936/…
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 15:55
















Perhaps an AFTER INSERT trigger would do that (UPDATE the column with the PK/identity value when it is null)? Like this: stackoverflow.com/questions/13249936/…
– yper-crazyhat-cubeᵀᴹ
Dec 18 at 15:55





Perhaps an AFTER INSERT trigger would do that (UPDATE the column with the PK/identity value when it is null)? Like this: stackoverflow.com/questions/13249936/…
– yper-crazyhat-cubeᵀᴹ
Dec 18 at 15:55











3 Answers
3






active

oldest

votes


















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





share|improve this answer




















  • I hadn't twigged that sequences would operate per-row like that instead of per-call-instance. Handy to know. Confirmed it works the same way with multiple rows in one insert with INSERT Users SELECT NEXT VALUE FOR SQ_temp, UserName+'Dup', NEXT VALUE FOR SQ_temp FROM Users; SELECT * FROM dbo.Users;
    – David Spillett
    Dec 19 at 14:20










  • This is nice to know but how are you proposing to use sequences to address the OP's problem?
    – Andriy M
    Dec 19 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
    – SQLing4ever
    Dec 19 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 at 16:20



















5















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.






share|improve this answer






















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


















-1














What about temporaryly disabling the FK Constraint check?



CREATE TABLE dbo.person (id int NOT NULL PRIMARY KEY, createdby int CONSTRAINT FK_TEST FOREIGN KEY REFERENCES dbo.person(id))

ALTER TABLE dbo.PERSON
NOCHECK CONSTRAINT FK_TEST

INSERT INTO dbo.person(id, createdby) values (1,2)
INSERT INTO dbo.person(id, createdby) values (2,1)

ALTER TABLE dbo.PERSON
WITH CHECK CHECK CONSTRAINT FK_TEST


Result



SELECT * FROM dbo.person

id createdby
1 2
2 1





share|improve this answer




















    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225274%2finsert-self-referencing-entry-into-sql-server%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    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





    share|improve this answer




















    • I hadn't twigged that sequences would operate per-row like that instead of per-call-instance. Handy to know. Confirmed it works the same way with multiple rows in one insert with INSERT Users SELECT NEXT VALUE FOR SQ_temp, UserName+'Dup', NEXT VALUE FOR SQ_temp FROM Users; SELECT * FROM dbo.Users;
      – David Spillett
      Dec 19 at 14:20










    • This is nice to know but how are you proposing to use sequences to address the OP's problem?
      – Andriy M
      Dec 19 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
      – SQLing4ever
      Dec 19 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 at 16:20
















    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





    share|improve this answer




















    • I hadn't twigged that sequences would operate per-row like that instead of per-call-instance. Handy to know. Confirmed it works the same way with multiple rows in one insert with INSERT Users SELECT NEXT VALUE FOR SQ_temp, UserName+'Dup', NEXT VALUE FOR SQ_temp FROM Users; SELECT * FROM dbo.Users;
      – David Spillett
      Dec 19 at 14:20










    • This is nice to know but how are you proposing to use sequences to address the OP's problem?
      – Andriy M
      Dec 19 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
      – SQLing4ever
      Dec 19 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 at 16:20














    5












    5








    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





    share|improve this answer












    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






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Dec 18 at 17:58









    SQLing4ever

    783




    783











    • I hadn't twigged that sequences would operate per-row like that instead of per-call-instance. Handy to know. Confirmed it works the same way with multiple rows in one insert with INSERT Users SELECT NEXT VALUE FOR SQ_temp, UserName+'Dup', NEXT VALUE FOR SQ_temp FROM Users; SELECT * FROM dbo.Users;
      – David Spillett
      Dec 19 at 14:20










    • This is nice to know but how are you proposing to use sequences to address the OP's problem?
      – Andriy M
      Dec 19 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
      – SQLing4ever
      Dec 19 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 at 16:20

















    • I hadn't twigged that sequences would operate per-row like that instead of per-call-instance. Handy to know. Confirmed it works the same way with multiple rows in one insert with INSERT Users SELECT NEXT VALUE FOR SQ_temp, UserName+'Dup', NEXT VALUE FOR SQ_temp FROM Users; SELECT * FROM dbo.Users;
      – David Spillett
      Dec 19 at 14:20










    • This is nice to know but how are you proposing to use sequences to address the OP's problem?
      – Andriy M
      Dec 19 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
      – SQLing4ever
      Dec 19 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 at 16:20
















    I hadn't twigged that sequences would operate per-row like that instead of per-call-instance. Handy to know. Confirmed it works the same way with multiple rows in one insert with INSERT Users SELECT NEXT VALUE FOR SQ_temp, UserName+'Dup', NEXT VALUE FOR SQ_temp FROM Users; SELECT * FROM dbo.Users;
    – David Spillett
    Dec 19 at 14:20




    I hadn't twigged that sequences would operate per-row like that instead of per-call-instance. Handy to know. Confirmed it works the same way with multiple rows in one insert with INSERT Users SELECT NEXT VALUE FOR SQ_temp, UserName+'Dup', NEXT VALUE FOR SQ_temp FROM Users; SELECT * FROM dbo.Users;
    – David Spillett
    Dec 19 at 14:20












    This is nice to know but how are you proposing to use sequences to address the OP's problem?
    – Andriy M
    Dec 19 at 19:15




    This is nice to know but how are you proposing to use sequences to address the OP's problem?
    – Andriy M
    Dec 19 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
    – SQLing4ever
    Dec 19 at 21:02





    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
    – SQLing4ever
    Dec 19 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 at 16:20





    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 at 16:20














    5















    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.






    share|improve this answer






















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















    5















    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.






    share|improve this answer






















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













    5












    5








    5







    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.






    share|improve this answer















    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.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Dec 19 at 14:27

























    answered Dec 18 at 16:00









    David Spillett

    22k23167




    22k23167











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
















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















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




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











    -1














    What about temporaryly disabling the FK Constraint check?



    CREATE TABLE dbo.person (id int NOT NULL PRIMARY KEY, createdby int CONSTRAINT FK_TEST FOREIGN KEY REFERENCES dbo.person(id))

    ALTER TABLE dbo.PERSON
    NOCHECK CONSTRAINT FK_TEST

    INSERT INTO dbo.person(id, createdby) values (1,2)
    INSERT INTO dbo.person(id, createdby) values (2,1)

    ALTER TABLE dbo.PERSON
    WITH CHECK CHECK CONSTRAINT FK_TEST


    Result



    SELECT * FROM dbo.person

    id createdby
    1 2
    2 1





    share|improve this answer

























      -1














      What about temporaryly disabling the FK Constraint check?



      CREATE TABLE dbo.person (id int NOT NULL PRIMARY KEY, createdby int CONSTRAINT FK_TEST FOREIGN KEY REFERENCES dbo.person(id))

      ALTER TABLE dbo.PERSON
      NOCHECK CONSTRAINT FK_TEST

      INSERT INTO dbo.person(id, createdby) values (1,2)
      INSERT INTO dbo.person(id, createdby) values (2,1)

      ALTER TABLE dbo.PERSON
      WITH CHECK CHECK CONSTRAINT FK_TEST


      Result



      SELECT * FROM dbo.person

      id createdby
      1 2
      2 1





      share|improve this answer























        -1












        -1








        -1






        What about temporaryly disabling the FK Constraint check?



        CREATE TABLE dbo.person (id int NOT NULL PRIMARY KEY, createdby int CONSTRAINT FK_TEST FOREIGN KEY REFERENCES dbo.person(id))

        ALTER TABLE dbo.PERSON
        NOCHECK CONSTRAINT FK_TEST

        INSERT INTO dbo.person(id, createdby) values (1,2)
        INSERT INTO dbo.person(id, createdby) values (2,1)

        ALTER TABLE dbo.PERSON
        WITH CHECK CHECK CONSTRAINT FK_TEST


        Result



        SELECT * FROM dbo.person

        id createdby
        1 2
        2 1





        share|improve this answer












        What about temporaryly disabling the FK Constraint check?



        CREATE TABLE dbo.person (id int NOT NULL PRIMARY KEY, createdby int CONSTRAINT FK_TEST FOREIGN KEY REFERENCES dbo.person(id))

        ALTER TABLE dbo.PERSON
        NOCHECK CONSTRAINT FK_TEST

        INSERT INTO dbo.person(id, createdby) values (1,2)
        INSERT INTO dbo.person(id, createdby) values (2,1)

        ALTER TABLE dbo.PERSON
        WITH CHECK CHECK CONSTRAINT FK_TEST


        Result



        SELECT * FROM dbo.person

        id createdby
        1 2
        2 1






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 18 at 15:47









        Randi Vertongen

        1,20312




        1,20312



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225274%2finsert-self-referencing-entry-into-sql-server%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown






            Popular posts from this blog

            How to check contact read email or not when send email to Individual?

            Displaying single band from multi-band raster using QGIS

            How many registers does an x86_64 CPU actually have?