Как сохранить автоматически сгенерированный идентификатор первичного ключа в столбце внешнего ключа в той же таблице

Question

Ниже приведена структура таблицы:

CREATE TABLE [User] (
        [Id] bigint identity(1,1) not null,
        [FirstName] nvarchar(100) not null,
        [LastName] nvarchar(100) not null,
        [Title] nvarchar(5) null,
        [UserName] nvarchar(100) not null,
        [Password] nvarchar(100) not null,      
        [Inactive] bit null,
        [Created] Datetime not null,
        [Creator] bigint not null,
        [Modified] DateTime null,
        [Modifier] bigint null
        CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
        (
            [Id] Asc
        )
    );

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_User_Creator]') AND parent_object_id = OBJECT_ID(N'[User]'))
    ALTER TABLE [User] ADD CONSTRAINT [FK_User_Creator] FOREIGN KEY([Creator]) REFERENCES [User]([Id])
GO


INSERT INTO [User] (Creator) Values ([Id] ?)

Это тот случай, когда таблица пуста, и первый пользователь собирается добавить ее в таблицу. В противном случае у меня нет проблемы.

Как одновременно вставить идентификатор в столбец создателя с оператором вставки?

Answer 1

Одним из способов может быть использование последовательности вместо идентификатора столбца. Приведенный ниже скрипт может служить той же цели:

CREATE SEQUENCE dbo.useridsequence  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

CREATE TABLE [User] (
        [Id] bigint DEFAULT (NEXT VALUE FOR dbo.useridsequence) ,
        [FirstName] nvarchar(100) not null,
        [LastName] nvarchar(100) not null,
        [Title] nvarchar(5) null,
        [UserName] nvarchar(100) not null,
        [Password] nvarchar(100) not null,      
        [Inactive] bit null,
        [Created] Datetime not null,
        [Creator] bigint DEFAULT NEXT VALUE FOR dbo.useridsequence ,
        [Modified] DateTime null,
        [Modifier] bigint null
        CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
        (
            [Id] Asc
        )
    );

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_User_Creator]') AND parent_object_id = OBJECT_ID(N'[User]'))
    ALTER TABLE [User] ADD CONSTRAINT [FK_User_Creator] FOREIGN KEY([Creator]) REFERENCES [User]([Id])
GO


INSERT INTO [User]
(
    -- Id -- this column value is auto-generated
    FirstName,
    LastName,
    Title,
    UserName,
    [Password],
    Inactive,
    Created,
    Creator,
    Modified,
    Modifier
)
VALUES
(
    'Foo',
    'Bar',
    'Title',
    'UserName ',
    'Password',
     0,
    GETDATE(),
    DEFAULT,
    GETDATE(),
    1 
)

SELECT * FROM [User] AS u

Результат:

Answer 2

Короткий ответ: вы не можете этого сделать. И я полагаю, что ваша модель в первую очередь имеет недостатки. Намереваетесь ли вы определить всех реальных пользователей базы данных (например, создать пользователя... для входа в систему...) как строки в [Users]? Вы должны подумать об этом - но типичный ответ - нет. Если ответ "да", то вам вообще не нужен столбец-создатель, потому что он избыточен. Все, что вам нужно, это дата создания - для которой вы, вероятно, должны были определить значение по умолчанию.

Но если вы хотите сделать это, вам нужно будет сделать это в два шага (и вам нужно будет сделать столбец обнуляемым). Вы вставляете строку (или строки) со значениями для "реальных" столбцов данных. Затем обновите те же строки значениями идентификаторов, созданными для идентификатора. Пример, показывающий разные способы сделать это

use tempdb;
set nocount on;
CREATE TABLE dbo.[user] (
        [user_id] smallint identity(3,10) not null primary key,
        [name] nvarchar(20) not null,
        [active] bit  not null default (1),
        [created] Datetime not null default (current_timestamp),
        [creator] smallint  null  
    );
ALTER TABLE dbo.[user] ADD CONSTRAINT [fk_user] FOREIGN KEY(creator) REFERENCES dbo.[user](user_id);
GO
-- add first row
insert dbo.[user] (name) values ('test');
update dbo.[user] set creator = SCOPE_IDENTITY() where user_id = SCOPE_IDENTITY()

-- add two more rows
declare @ids table (user_id smallint not null);
insert dbo.[user] (name) output inserted.user_id into @ids
values ('nerk'), ('pom');
update t1 set creator = t1.user_id
from @ids as newrows inner join dbo.[user] as t1 on newrows.user_id = t1.user_id;
select * from dbo.[user] order by user_id;

-- mess things up a bit
delete dbo.[user] where name = 'pom';

-- create an error, consume an identity value
insert dbo.[user](name) values (null);

-- add 2 morerows
delete @ids; 
insert dbo.[user] (name) output inserted.user_id into @ids
values ('nerk'), ('pom');
update t1 set creator = t1.user_id
from @ids as newrows inner join dbo.[user] as t1 on newrows.user_id = t1.user_id;

select * from dbo.[user] order by user_id;
drop table dbo.[user];

И я изменил спецификацию идентичности, чтобы продемонстрировать то, что понимают немногие разработчики. Он не всегда определяется как (1,1), и следующее вставленное значение может прыгать по многим причинам - например, ошибки и кэширование / перезапуск. Наконец, я думаю, вы пожалеете, что назвали таблицу зарезервированным словом, так как для ссылок на него потребуется использовать разделители. Уменьшить боль.