13 Replies - 1630 Views - Last Post: 04 December 2010 - 02:32 PM

#1 samforever1989  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 53
  • Joined: 25-November 10

prevent identity column by auto inserting

Posted 04 December 2010 - 10:39 AM

sir,
I am facing a problem with identity and my problem is ...
If an identity column exists for a table with frequent deletions, gaps can occur between identity values . I want to remove these gaps mean I want to continue counting from where the gap exits..

mean unwanted gap when deleting a row using identity column

can you suggest something..??

This post has been edited by samforever1989: 04 December 2010 - 10:44 AM

Is This A Good Question/Topic? 0
  • +

Replies To: prevent identity column by auto inserting

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6033
  • View blog
  • Posts: 23,414
  • Joined: 23-August 08

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 11:50 AM

Duplicate topic

Outside of the suggestions in the other topic, the other option is a "soft delete", that is add a boolean column to the database to represent whether a record is deleted, rather than actually removing the record from the database.

Although you should probably speak to your instructor, because it sounds like either he's an idiot, or you're misunderstanding something.
Was This Post Helpful? 0
  • +
  • -

#3 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1252
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 11:50 AM

Yeah I guess this belongs more here. Same answers as we gave before.
Was This Post Helpful? 0
  • +
  • -

#4 samforever1989  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 53
  • Joined: 25-November 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:00 PM

View PostJackOfAllTrades, on 04 December 2010 - 10:50 AM, said:

Duplicate topic

Outside of the suggestions in the other topic, the other option is a "soft delete", that is add a boolean column to the database to represent whether a record is deleted, rather than actually removing the record from the database.

Although you should probably speak to your instructor, because it sounds like either he's an idiot, or you're misunderstanding something.


sir I tell you what is the problem..
I am developing library management system project. In it I have to delete a book from the front end (window form) so if it had id - b005 so I want to provide this book id to another book (to the very next book and that book's id to it's next and so on...)

I hope got my exact problem this time..
Was This Post Helpful? 0
  • +
  • -

#5 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1252
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:05 PM

If you did that you would have legacy issues. Imagine two years down the line after this has been in active service you boss asks you, "Hey, I want to see a report of all the people who rented out B005 back in 2008."

If you change the ID's like that, those older reports immediately lose all value because right now, in 2010, B005 might belong to another book. See the problem?

Leave ID's to their thing :P

This post has been edited by Sergio Tapia: 04 December 2010 - 12:05 PM

Was This Post Helpful? 0
  • +
  • -

#6 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:06 PM

you could load a table variable with the table data, truncate the original table, then reload it from the table variable, but as others have said in the other post, there's really not a point to doing it. Every entry has a unique primary key and arbitrarily changing the primary key on an entry could lead to problems. The only time I've bothered with resetting a table in this way was when I was resetting test data.

This post has been edited by Nakor: 04 December 2010 - 12:09 PM

Was This Post Helpful? 0
  • +
  • -

#7 samforever1989  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 53
  • Joined: 25-November 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:12 PM

View PostSergio Tapia, on 04 December 2010 - 11:05 AM, said:

If you did that you would have legacy issues. Imagine two years down the line after this has been in active service you boss asks you, "Hey, I want to see a report of all the people who rented out B005 back in 2008."

If you change the ID's like that, those older reports immediately lose all value because right now, in 2010, B005 might belong to another book. See the problem?

Leave ID's to their thing :P

You are absolutely right but in some other situations it could be useful like if we insert some data into the table where an identity column exits...
If there an error occur in inserting the values to the table like we did not apply quotes around the string values , in this situation no data will be submitted but this auto generated column(identity column) inserts its values ...
That should not be happened...
Was This Post Helpful? 0
  • +
  • -

#8 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:15 PM

You could create a bookID field that is separate from the primary key field that could be applied to different books, and as I believe someone else has said, add a boolean value to determine if the that book/bookID combination is currently active or not.

View Postsamforever1989, on 04 December 2010 - 12:12 PM, said:

View PostSergio Tapia, on 04 December 2010 - 11:05 AM, said:

If you did that you would have legacy issues. Imagine two years down the line after this has been in active service you boss asks you, "Hey, I want to see a report of all the people who rented out B005 back in 2008."

If you change the ID's like that, those older reports immediately lose all value because right now, in 2010, B005 might belong to another book. See the problem?

Leave ID's to their thing :P

You are absolutely right but in some other situations it could be useful like if we insert some data into the table where an identity column exits...
If there an error occur in inserting the values to the table like we did not apply quotes around the string values , in this situation no data will be submitted but this auto generated column(identity column) inserts its values ...
That should not be happened...


This is why data should be validated before inserting it into the database
Was This Post Helpful? 0
  • +
  • -

#9 samforever1989  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 53
  • Joined: 25-November 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:16 PM

View PostNakor, on 04 December 2010 - 11:15 AM, said:

You could create a bookID field that is separate from the primary key field that could be applied to different books, and as I believe someone else has said, add a boolean value to determine if the that book/bookID combination is currently active or not.

View Postsamforever1989, on 04 December 2010 - 12:12 PM, said:

View PostSergio Tapia, on 04 December 2010 - 11:05 AM, said:

If you did that you would have legacy issues. Imagine two years down the line after this has been in active service you boss asks you, "Hey, I want to see a report of all the people who rented out B005 back in 2008."

If you change the ID's like that, those older reports immediately lose all value because right now, in 2010, B005 might belong to another book. See the problem?

Leave ID's to their thing :P

You are absolutely right but in some other situations it could be useful like if we insert some data into the table where an identity column exits...
If there an error occur in inserting the values to the table like we did not apply quotes around the string values , in this situation no data will be submitted but this auto generated column(identity column) inserts its values ...
That should not be happened...


This is why data should be validated before inserting it into the database


but how..???
any example...??
Was This Post Helpful? 0
  • +
  • -

#10 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1252
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:21 PM

An example of validating your data before inserting it? Just be extra careful, always assume the end user is a dumb squirrel and ALWAYS sanitize your inputs.
Was This Post Helpful? 0
  • +
  • -

#11 samforever1989  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 53
  • Joined: 25-November 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:27 PM

View PostSergio Tapia, on 04 December 2010 - 11:21 AM, said:

An example of validating your data before inserting it? Just be extra careful, always assume the end user is a dumb squirrel and ALWAYS sanitize your inputs.


So there is no way to do so....??
any stored procedure or some code....??

I got this on microsoft's website... But it went beyond my head...




-- Here is the generic syntax for finding identity value gaps in data.
-- This is the beginning of the illustrative example.
SET IDENTITY_INSERT tablename ON

DECLARE @minidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN(IDENTITYCOL) FROM tablename
IF @minidentval = IDENT_SEED('tablename')
   SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('tablename')
   FROM tablename t1
   WHERE IDENTITYCOL BETWEEN IDENT_SEED('tablename') AND 
      MAX(column_type) AND
      NOT EXISTS (SELECT * FROM tablename t2
         WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + 
            IDENT_INCR('tablename'))
ELSE
   SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column 
-- called id_num, which is an increasing identification number, and the 
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'img')
   DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN(IDENTITYCOL) FROM img
 IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('img')
    FROM img t1
    WHERE IDENTITYCOL BETWEEN IDENT_SEED('img') AND 32766 AND
      NOT    EXISTS (SELECT * FROM img t2
          WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('img'))
 ELSE
    SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF




Was This Post Helpful? 0
  • +
  • -

#12 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:35 PM

are you doing everything on the database end or is there web/application code associated with this also. It's usually a little easier to validate the input in the code before ever sending it to the database, at least in my opinion.

Also, make sure your database is set up correctly not to allow null values in columns that need to have a value. Use unique constraints where appropriate to prevent duplicate entries.
Was This Post Helpful? 0
  • +
  • -

#13 samforever1989  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 53
  • Joined: 25-November 10

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 12:47 PM

View PostNakor, on 04 December 2010 - 11:35 AM, said:

are you doing everything on the database end or is there web/application code associated with this also. It's usually a little easier to validate the input in the code before ever sending it to the database, at least in my opinion.

Also, make sure your database is set up correctly not to allow null values in columns that need to have a value. Use unique constraints where appropriate to prevent duplicate entries.


I tried but still having gap...
Was This Post Helpful? 0
  • +
  • -

#14 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: prevent identity column by auto inserting

Posted 04 December 2010 - 02:32 PM

what does the insert statement you're using look like?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1