logo标签列表

自引用表中的插入

sqlsql-server
9
如果我有一个表格
Table
{
ID int primary key identity,
ParentID int not null foreign key references Table(ID)
}

如何向表格中插入第一行？

从业务逻辑角度看，不应该删除ParentID的非空约束。

1
有趣的问题。你能否引用自己？如果它是新创建的，你如何创建1条记录？ - YvesR
2
你的第一行的父行是哪一行？ - Mikael Eriksson
2
对于第一行，ParentID 应该是 ID。如果我想在我的树形结构中使用多个根节点，那么任何一个根节点的 ParentID == ID。因此，问题可以扩展为 - 如何插入根行？存储过程能否使用 SCOPE_IDENTITY 来进行此插入？ - Nezreli
@Nezreli 我已经更新了我的答案以满足你的需求。有一件事我不是完全确定的是2005是否与我目前使用的2008R2有所不同。如果你能验证一下，那就太感激了。 - Serge Belov
@Nezreli，你解决了这个问题吗？结果如何？ - Serge Belov
我会尝试在一个例子中使用你的解决方案，看看它是否足够稳定以进行广泛实施。 - Nezreli
3个回答
5
在SQL Server中，一个简单的INSERT语句就可以实现：
create table dbo.Foo
(
ID int primary key identity,
ParentID int not null foreign key references foo(ID)
)
go

insert dbo.Foo (parentId) values (1)

select * from dbo.Foo

导致
    ID          ParentID
----------- -----------
    1           1

如果您尝试插入一个与身份种子不同的值，插入操作将失败。
更新:
问题不是很清楚上下文是什么(即代码是否应该在实时生产系统中工作还是只是数据库设置脚本)，从评论中看来，硬编码ID可能不是一个选项。虽然上面的代码通常在需要知道层次结构根ID并使其保持不变的DB初始化脚本中可以正常工作，但对于森林(多个根，其ID事先不知)情况下，以下内容应该可以按预期工作：
create table dbo.Foo
(
ID int primary key identity,
ParentID int not null foreign key references foo(ID)
)
go

insert dbo.Foo (parentId) values (IDENT_CURRENT('dbo.Foo'))

然后，像往常一样查询最后的标识符（SCOPE_IDENTITY等）。为了解决@usr的顾虑，事实上以下示例证明该代码在事务安全方面是可靠的：

insert dbo.Foo (parentId) values (IDENT_CURRENT('dbo.Foo'))
insert dbo.Foo (parentId) values (IDENT_CURRENT('dbo.Foo'))
insert dbo.Foo (parentId) values (IDENT_CURRENT('dbo.Foo'))

select * from dbo.Foo

select IDENT_CURRENT('dbo.Foo')
begin transaction   
    insert dbo.Foo (parentId) values (IDENT_CURRENT('dbo.Foo'))
    rollback

select IDENT_CURRENT('dbo.Foo')

insert dbo.Foo (parentId) values (IDENT_CURRENT('dbo.Foo'))

select * from dbo.Foo

结果：

ID          ParentID
----------- -----------
1           1
2           2
3           3

currentIdentity
---------------------------------------
3

currentIdentity
---------------------------------------
4

ID          ParentID
----------- -----------
1           1
2           2
3           3
5           5
1
这取决于第一个ID为1，但这并不保证。插入失败（超时）可能会破坏第一个ID的值，使表永久损坏。 - usr
@usr，这取决于使用默认种子1的身份验证方式，就像问题中使用的那样。在两个位置替换种子仍然有效。答案中也提到了这一点。但我对这种情况会导致表格损坏的详细信息非常感兴趣，请您详细说明一下？ - Serge Belov
向表中插入一行，回滚事务。这将浪费一个标识值。由于并发原因，标识值永远不会回滚。 - usr
@usr 在这种情况下，表格不会“损坏”，您只需拥有一个不同的当前身份，可以通过多种方式解决。 - Serge Belov
1
是的，它需要查询当前标识值。这很尴尬，也不具备事务一致性。没有办法原子地查询该值并插入一行。我认为，几乎任何依赖于下一个标识值确切值的代码都是根本上有问题的。可能会有例外情况，但出于我所述的原因，这种情况不是其中之一。 - usr
根据我的经验，自引用表通常在数据库设置脚本中初始化，这时候你提到的任何问题都不重要了。无论如何，我认为我们现在开始讨论的是观点而不是事实。 - Serge Belov
2
似乎树的根节点不符合 NOT NULL 约束。它没有父节点，因此假设 ParentID 为 NOT NULL 从一开始就是错误的。
我建议您将其设置为可空，并在 ParentID 上添加索引以验证只有一个具有值 NULL ：
create unique nonclustered index ... on T (ParentID) where (ParentID IS NULL)

在 SQL Server 中很难强制执行一个良好的树形结构。例如，您可能会得到多个根或图形中的循环。验证所有这些是困难的，而且不清楚这是否值得一试。根据具体情况，这可能是值得的努力。
当然，要使用过滤索引，必须使用SQL Server 2008或更高版本... - Andriy M
有趣的解决方案，但客户使用的是SQL Server 2005。 - Nezreli
@Nezreli，我建议使用触发器。触发器可以查看插入的行以验证基本上任何内容。它可能看起来像这样：IF (SELECT COUNT(*) FROM T WHERE ParentID IS NULL) > 1 RAISEERROR。 - usr
2
如果您需要为第一个ID使用显式值，当您插入第一条记录时，您可以禁用IDENTITY值的检查（请参阅：MSDN: SET IDENTITY_INSERT (Transact-SQL)）。
以下是说明此示例的示例：
CREATE TABLE MyTable
(
  ID int PRIMARY KEY IDENTITY(1, 1),
  ParentID int NOT NULL,
  CONSTRAINT MyTable_ID FOREIGN KEY (ParentID) REFERENCES MyTable(ID)
);

SET IDENTITY_INSERT MyTable ON;
INSERT INTO MyTable (ID, ParentID)
VALUES (1, 1);
SET IDENTITY_INSERT MyTable OFF;

WHILE @@IDENTITY <= 5
BEGIN
    INSERT INTO MyTable (ParentID)
    VALUES (@@IDENTITY);
END;

SELECT *
  FROM MyTable;

IF OBJECT_ID('MyTable') IS NOT NULL
    DROP TABLE MyTable;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接