InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:10:37 GMT20http://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxYeah, the not null constraint is tricky.If you really need it, try using GUIDs instead of an identity column. GUIDs (uniqueidentifier datatype) can be generated in the application, so you don't have to wait for the row to be populated in order to have the "ID" value.Wed, 02 Jan 2013 07:11:51 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxYou could use an AFTER INSERT trigger to insure that the parent id got set to the identity column values ... but you still couldn't define the parent id as "NOT NULL", of course.Even with an INSTEAD OF INSERT trigger, you run into timing issues on when the identity column value is assigned.Fri, 28 Dec 2012 15:19:57 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxSince the usual model is NULL = top level, there isn't a built-in method to bypass that, so you're going to be stuck with custom code and solutions.If you want to use the 0 or -1 version, you'd just have to create a row with that value, self-reference it, and then ignore it for all practical purposes. Would allow the FK to work. Wouldn't make querying any faster/easier, just would make inserting parent-rows easier, since they'd have a fixed value.Fri, 28 Dec 2012 06:50:53 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxIn fact, neither works when inserting >1 parents.I was simply wondering if I could save myself some code and a step with some fancy DRI.Now I see that while it can be made to work, it's probably more trouble than it's worth.(And you can't just throw a 0 or -1 in there because of the FK.)Thanks for having a look at this.PThu, 27 Dec 2012 11:15:12 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxYou can do what you're trying. Try this:[code="sql"]USE ProofOfConcept;GOCREATE TABLE dbo.SelfReferencing (ID INT IDENTITY PRIMARY KEY, ParentID INT NOT NULL DEFAULT (IDENT_CURRENT('dbo.SelfReferencing')) REFERENCES dbo.SelfReferencing (ID), RowName VARCHAR(50));GOINSERT INTO dbo.SelfReferencing (ParentID, RowName)VALUES (DEFAULT, 'Row1: Parent'), (1, 'Row2: Child');GOSELECT *FROM dbo.SelfReferencing;[/code]You can end up with problems with this one, if Ident_Current runs into a conflict between multiple concurrent inserts. So you'd need to pick a very strict locking scheme for every insert.You can also use Scope_Identity in that. Like so:[code="sql"]USE ProofOfConcept;GOIF OBJECT_ID(N'dbo.SelfReferencing') IS NOT NULL DROP TABLE dbo.SelfReferencing;GOCREATE TABLE dbo.SelfReferencing (ID INT IDENTITY PRIMARY KEY, ParentID INT NOT NULL DEFAULT (SCOPE_IDENTITY()) REFERENCES dbo.SelfReferencing (ID), RowName VARCHAR(50));GOINSERT INTO dbo.SelfReferencing (ParentID, RowName)VALUES (DEFAULT, 'Row1: Parent'), (1, 'Row2: Child');GOSELECT *FROM dbo.SelfReferencing;[/code]If you test that, you'll find the problem with it immediately. Because the multi-row insert is treated as a single transaction (of course), the first row, which is supposed to be self-referent (a parent the way you do it), ends up being a child of the last row inserted. Not what you want.The usual way to handle adjacency hierarchies (which is what this is), is to make parent rows have a NULL value in the ParentID column. Alternatively, if you don't want NULLs, you can put an arbitrary value in there, like 0 or -1. Since NULL technically means "unknown value" as well as "confirmed no value", that can represent the data better in some models. But NULL is usual, and works just fine in the vast majority of cases.Thu, 27 Dec 2012 10:26:30 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxThanks Sean.Thu, 27 Dec 2012 08:42:53 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxWhat you are describing is known as an adjacency list. They are frequently used in sql for a parent-child relationship. If you make the ParentID not null you can't have a starting point. Meaning that every row MUST have a parent. Somewhere there has to be a row that has no parent. I still don't really understand what you are trying to do but I can say that it isn't going to work like you trying.Thu, 27 Dec 2012 08:36:15 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxI'm trying to set up a parent-child relationship. Some rows will be parents (nId = nParent), and some rows will be descendants (nId <> nParent).In the past, I've always created the table with nParent NULL. To create a parent, I insert a row, and immediately update nParent to the newly created identity. (When creating a descendant, the nParent is always known.)I was simply wondering if there was a way to create a parent in a single step.Thu, 27 Dec 2012 08:25:59 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxWhat are you trying to do here? I don't understand what the default of SCOPE_IDENTITY or @@IDENTITY are trying to do. I don't understand what you are trying to do but doing it like this will never work. You have those columns as NOT NULL. That means that in order for this to work you will always have to an insert in the batch that inserts to a table that has an identity prior to inserting to this table.If you can explain what you are trying to do we can probably help.Thu, 27 Dec 2012 08:19:56 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1400619-391-1.aspxHi all,I'm wondering if there's a way to do the following:IF OBJECT_ID('test') IS NOT NULLDROP TABLE testGOCREATE TABLE test (nId INTEGER IDENTITY NOT NULL, nData INTEGER NOT NULL, nParent INTEGER NOT NULL)GOALTER TABLE testADD CONSTRAINT PK_Test_nIdPRIMARY KEY (nId)GOALTER TABLE testADD CONSTRAINT DF_test_ParentDEFAULT SCOPE_IDENTITY() FOR nParent--DEFAULT @@IDENTITY FOR nParentGOALTER TABLE testADD CONSTRAINT FK_test_ParentFOREIGN KEY (nParent)REFERENCES test (nId)GOINSERT test (nData)SELECT 2goEither way, I get an error since SCOPE_IDENTITY() / @@IDENTITY are not set until the INSERT completes. Is there some way to make this scenario work only using DRI?Thanks!PThu, 27 Dec 2012 07:43:07 GMTschleep