Insertion into a table with self-accrual

Question

If i have a table

Table { ID int primary key identity, ParentID int not null foreign key references Table(ID) } 

how to insert the first row into the table?

From a business logic perspective, a non-zero ParentID constraint should not be excluded.

Answer 1

In SQL Server, a simple INSERT will do:

 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 

leads to

  ID ParentID ----------- ----------- 1 1 

If you try to insert a value that will be different from your identification seed, the insert will fail.

UPDATE:

The question is not too clear as to what the context is (i.e. it is code that should work in a live production system or just a DB configuration script), and from comments that seem difficult to code, the identifier may not be an option . Although the above code, as a rule, works fine in DB initialization scenarios, where the root hierarchy identifier can be known and constant, in the case of a forest (several roots with identifiers that are unknown in advance), the following should work as intended:

 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')) 

You can then query for the last name as usual ( SCOPE_IDENTITY , etc.). To solve @usr problems, the code is really transaction-safe, as shown in the following example:

 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 

Result:

 ID ParentID ----------- ----------- 1 1 2 2 3 3 currentIdentity --------------------------------------- 3 currentIdentity --------------------------------------- 4 ID ParentID ----------- ----------- 1 1 2 2 3 3 5 5 

Answer 2

It seems that the NOT NULL is incorrect for the root of the node in the tree. It just doesn't have a parent. Thus, the assumption that ParentID NOT NULL broken from the very beginning.

I suggest you make it null and add an index on the ParentID to verify that there is only one with a NULL value:

 create unique nonclustered index ... on T (ParentID) where (ParentID IS NULL) 

It is difficult to create a sound tree structure in SQL Server. You can get multiple roots, for example, or loops on a graph. It is difficult to verify all this, and it is not clear whether it is worth doing. It is possible, depending on the specific case.

Answer 3

If you need to use an explicit value for the first identifier when you insert your first record, you can turn off checking the IDENTITY value (see MSDN: SET IDENTITY_INSERT (Transact-SQL) ).

Here is an example that is not suitable for this:

 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;