Self referencing tables & Identity columns
-
Hi
I have a situation that you might find interesting. I have a table, say
Category, that references itself as follows...
CREATE TABLE Category
(
CategoryID int NOT NULL,
Name varchar(25) NOT NULL,
ParentCategoryID int NOT NULL
CONSTRAINT PK_CATEGORY PRIMARY KEY (CategoryID),
CONSTRAINT FK_CATEGORY_ID FOREIGN KEY (ParentCategoryID) REFERENCES
Category(CategoryID)
)
The ParentCategoryID field refers to another record in the same table.
This is to maintain a hierarchy of Categories, with all categories except the
ROOT category maintaining a reference to their "Parent Category". The "ROOT"
references itself.
Now, I made the CategoryID column an IDENTITY column. Here's where things get
interesting. Let's say CategoryID is defined as follows...
CategoryID int NOT NULL IDENTITY (1,1)
Now, the following insert succeeds...
INSERT INTO CATEGORY
VALUES (1, 'CAT-1', 1) --- Self referencing ROOT node
Now, delete the just inserted record...
DELETE FROM CATEGORY
Now, the same INSERT fails...
INSERT INTO CATEGORY
VALUES (1, 'CAT-1', 1) --- Self referencing ROOT node
with the error....
INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint
'FK_CATEGORY_ID'.
Strangely, SELECT IDENT_CURRENT('Category') returns "1"...
a) Before the first INSERT
b) After the INSERT
c) After the DELETE
So, the question is....
Why does the subsequent (second) INSERT statement fail?
Also surprising is the fact that after the failed second INSERT statement,
SELECT IDENT_CURRENT('Category') returns "2"...
All help appreciated...
Vivian
-
Im no expert, but when you insert the first time, does it not some how reference to the foreign key, so when you delete it there is still some kind of instance remaining, therefore preventing you from performing another insert?
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
I've seen this sort of thing before, and I don't know if I like it that much. You'll get these delete errors, but then the 2nd time you run the delete it will work correctly. Weird stuff.
I question that "self referencing" FK, though; it seems like it's going to be a resource hog, and you could have serious problems with "feedback". The idea of a self-referencing table is good, but maybe you should just drop that FK reference.
That's probably flying in the face of public opinion, but it seems like a good idea to me.
Signature is NULL
-
The Identity Seed Gets incremented every time you insert a record!
if you want to reuse the Identity value that you deleted then you must run:
DBCC CHECKIDENT ('able Name', RESEED, 'reuse value')
I believe that you don't have to drop the Foreign Key constraint, just be AWARE that you should be able to live with non contiguos values and in case you want to delete a category then it will be very cubersome because you will have to wipe out all Childs ( I am almost positive that you don't want to do that, but hey that's your call
)
* Noel
)Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply