Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not null Foreign key self-referencing identity column Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 7:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 428, Visits: 924
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
(nId INTEGER IDENTITY NOT NULL,
nData INTEGER NOT NULL,
nParent INTEGER 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




Post #1400619
Posted Thursday, December 27, 2012 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400628
Posted Thursday, December 27, 2012 8:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 428, Visits: 924
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.




Post #1400633
Posted Thursday, December 27, 2012 8:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1400641
Posted Thursday, December 27, 2012 8:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 428, Visits: 924
Thanks Sean.





Post #1400649
Posted Thursday, December 27, 2012 10:26 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1400698
Posted Thursday, December 27, 2012 11:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 428, Visits: 924
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



Post #1400713
Posted Friday, December 28, 2012 6:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1400896
Posted Friday, December 28, 2012 3:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 1,949, Visits: 2,884
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1401080
Posted Wednesday, January 2, 2013 7:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1401825
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse