May 14, 2008 at 1:11 pm
I have 1 simple problem, and little bit confused why it happend!
There is situation:
I create the table like this ....
CREATE TABLE KONTAKT
(
IDK INT IDENTITY(1,1) NOT NULL,
FNAME VARCHAR(20),
SNAME VARCHAR (20),
SEX CHAR(1),
CONSTRAINT pk_IDKONTAKT PRIMARY KEY (IDK),
CONSTRAINT CHK_GJINIA CHECK (SEX = 'M' OR SEX = 'F')
);
and I try to insert values like:
INSERT INTO KONTAKT
VALUES ('aaa', 'aaaaa', 'M');
GO
SELECT * FROM KONTAKT;
GO
ok in this situtation you have the IDK = 1
if you try to insert values like:
INSERT INTO KONTAKT
VALUES ('aaa', 'aaaaa', 'G');
GO
The check constraint will fire and the values will not inserted but the IDK as Identity Column with grow by 1 and if you try to insert values regularly such as:
INSERT INTO KONTAKT
VALUES ('bbb, 'bbbbb', 'F');
GO
SELECT * FROM KONTAKT;
GO
You will see that the IDK = 3
=================================
Any explain where is gone the IDK = 2?
:w00t:
May 14, 2008 at 1:34 pm
The attempt to insert a row incremented the value. It then rolled back because of the constraint violation, but the increment remained. Identity fields will increment, but they don't guarantee gap free increments.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2008 at 1:39 pm
Grant Fritchey (5/14/2008)
The attempt to insert a row incremented the value. It then rolled back because of the constraint violation, but the increment remained. Identity fields will increment, but they don't guarantee gap free increments.
...which is ultimately a good thing. You don't want to end up with a system that "reuses" ID's, since it could create evil twins (links between data that shouldn't exist), such as related ID's which aren't bound by a formalized foreign key (like - mis-linking this stuff to archived data from the previous user of that ID value)....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply