代码之家 › 专栏 › 技术社区 › Furqan Misarwala

如何在同一表的外键列中保存自动生成的主键Id

sql-server-2008-r2 sql-server-2008 sql-server sql

Furqan Misarwala · 技术社区 · 7 年前

以下是表格结构:

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] ?)

2 回复 | 直到 7 年前

Khorshed Alam 7 年前

一种方法是使用序列而不是标识列。以下脚本可能具有相同的用途:

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

结果:

SMor 7 年前

简而言之,你不能这么做。首先,我认为你的模型在逻辑上有缺陷。您是否打算定义所有实际的数据库用户(例如,为登录创建用户…)作为[用户]中的行?你需要考虑一下,但典型的答案是否定的。如果答案是肯定的,那么你根本不需要creator列,因为它是多余的。您所需要的只是创建日期——您可能应该为其定义一个默认值。

但是,如果您想这样做,需要分两个步骤来完成(并且需要使列可以为null)。插入一行(或多行),其中包含“实”数据列的值。然后用为id生成的标识值更新这些相同的行

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),下一个插入的值可能由于许多原因而跳转,例如错误和缓存/重启。最后,我认为您会后悔用保留字命名表,因为对它的引用将需要使用分隔符。减轻疼痛。