December 27, 2012 at 7:43 am
Hi all,
I'm wondering if there's a way to do the following:
IF OBJECT_ID('test') IS NOT NULL
DROP TABLE test
GO
CREATE TABLE test
(nIdINTEGER IDENTITY NOT NULL,
nDataINTEGER NOT NULL,
nParentINTEGER NOT NULL)
GO
ALTER TABLE test
ADD CONSTRAINT PK_Test_nId
PRIMARY KEY (nId)
GO
ALTER TABLE test
ADD CONSTRAINT DF_test_Parent
DEFAULT SCOPE_IDENTITY() FOR nParent
--DEFAULT @@IDENTITY FOR nParent
GO
ALTER TABLE test
ADD CONSTRAINT FK_test_Parent
FOREIGN KEY (nParent)
REFERENCES test (nId)
GO
INSERT test (nData)
SELECT 2
go
Either 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!
P
December 27, 2012 at 8:19 am
What 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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2012 at 8:26 am
I'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.
December 27, 2012 at 8:36 am
What 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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2012 at 8:42 am
Thanks Sean.
December 27, 2012 at 10:26 am
You can do what you're trying. Try this:
USE ProofOfConcept;
GO
CREATE TABLE dbo.SelfReferencing
(ID INT IDENTITY
PRIMARY KEY,
ParentID INT NOT NULL
DEFAULT (IDENT_CURRENT('dbo.SelfReferencing'))
REFERENCES dbo.SelfReferencing (ID),
RowName VARCHAR(50));
GO
INSERT INTO dbo.SelfReferencing
(ParentID, RowName)
VALUES (DEFAULT, 'Row1: Parent'),
(1, 'Row2: Child');
GO
SELECT *
FROM dbo.SelfReferencing;
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:
USE ProofOfConcept;
GO
IF OBJECT_ID(N'dbo.SelfReferencing') IS NOT NULL
DROP TABLE dbo.SelfReferencing;
GO
CREATE TABLE dbo.SelfReferencing
(ID INT IDENTITY
PRIMARY KEY,
ParentID INT NOT NULL
DEFAULT (SCOPE_IDENTITY())
REFERENCES dbo.SelfReferencing (ID),
RowName VARCHAR(50));
GO
INSERT INTO dbo.SelfReferencing
(ParentID, RowName)
VALUES (DEFAULT, 'Row1: Parent'),
(1, 'Row2: Child');
GO
SELECT *
FROM dbo.SelfReferencing;
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 27, 2012 at 11:15 am
In 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.
P
December 28, 2012 at 6:50 am
Since 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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2012 at 3:19 pm
You 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.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 2, 2013 at 7:11 am
Yeah, 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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply