1

Here's some example code:

$sql = "INSERT INTO Users (Firstname,Surname) VALUES ('$firstname', '$surname')";

$stmt = sqlsrv_query( $conn, $sql,array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
if(!$stmt) 
{
die('Yu feiru!');
}

There are 3 columns in the database; ID, firstname and surname. ID is an auto increment primary key. I also have firstname as a unique key. So, now that I've put this all into perspective, all explain my problem.

When my form is complete it goes to the page were the above code is executed. Now, initially this works, as it should, so now I'll have my first record in the table with ID as 1. Now, if I am to refresh this page it returns an error, this is obviously because I am inserting the same firstname, and since it's a unique key it won't work, so far so good.

Here's the problem, if I am now to return back to the form and submit it with new details, the new record displays an ID of 3. So the problem is here that every time I run the query, whether it is successful or not, it increments the ID. So before, if I were to refresh the page 5 time, then the new record would have had an ID of 7.

EDIT: Please note, as I have stated, this is just example code. I still haven't taken any precautions against SQL injections. Regardless of this, thank you very much for your recommendations.

aelsheikh
  • 2,268
  • 5
  • 26
  • 41
  • 2
    Your script is prone to SQL-Injections. Please make sure that you properly escape the Input. Preferable by using [Prepared Statements](http://en.wikipedia.org/wiki/Prepared_statement). – TimWolla Feb 06 '12 at 19:01
  • are you sure the dup record isn't being inserted. your if statement should also be this `$stmt === false` as the `!` can work with many different things. – Henesnarfel Feb 06 '12 at 19:02
  • An auto-incrementing ID doesn't care if numbers are skipped. It wouldn't matter if your refreshed 300 times and missed 300 numbers. It can be bothersome to a very detail oriented person, but it truly doesn't matter at all if IDs are skipped. – Jim D Feb 06 '12 at 19:03
  • @TimWolla it's possible he's escaping those variables before using them in this query, but good note regardless. – Jim D Feb 06 '12 at 19:05
  • It's expected behavior: http://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback – Daniel Hilgarth Feb 06 '12 at 19:06
  • 1
    @JimD Better a note to much ;) – TimWolla Feb 06 '12 at 19:08

3 Answers3

1

I can give you a possible solution. BUT: first of all, there are VERY few reasons for you to want that. An ID's purpose is just to identify, NOT to provide a realiable-unique-sequence. If you want a gapless-unique-sequence you could do it yourself in other way.

That being said, adding this trigger:

CREATE TRIGGER IdentityFixer
   ON  dbo.YourTable
   AFTER INSERT
AS 
BEGIN
declare @NextID bigint
set @NextID = (select MAX(MyID) from dbo.YourTable)
DBCC CHECKIDENT ("dbo.YourTable", RESEED, @NextID);
END
GO

Will perfectly solve your said "problem" (in Microsoft SQL Server). You could insert and fail 1000 times and still your id will be gapless. I DON'T RECOMMEND THIS.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Gaspa79
  • 5,488
  • 4
  • 40
  • 63
1

This is by design. The IDENTITY column is assigned when the INSERT of each row is started, regardless of whether a COMMIT occurs. Any subsequent INSERTs will get numbers which are guaranteed not to conflict. Any or all of the transactions could fail or be rolled back. Without this ability, you would not be able to rely on the IDENTITY column to work with simultaneous operations.

I would strongly question any design goal that requires an unbroken sequence. Such things are very rarely truly necessary. I find the request usually comes from audit needs or from some kind of design which likes to assume some kind of row number/count of rows correlation. Typically, strong audit features can be implemented without relying on such a thing.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
0

You can make use of SQL transaction, commit and rollback using a try/catch statement and this will resolve all issues.

Sureround
  • 119
  • 6