Primary key doesn't increment

Question

I have problem with primary key column in my table. I set id column to be primary key ( I create table in Management studio and set there ), is identity to yes, increment to 1, seed to 1 , but when I try to insert it doesn't want to insert ( insert just once and doesn't increment value for id ). What to do ? This is from Managememt studio .

/****** Object:  Table [dbo].[club]    Script Date: 01/01/2011 22:00:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[club](
    [id] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [id_city] [int] NOT NULL,
    [street] [varchar](50) NULL,
    [street_number] [nchar](10) NULL,
    [descritpion] [varchar](500) NULL,
    [logo_path] [varchar](50) NULL,
 CONSTRAINT [PK_club] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

What do you mean it isn't incrementing? In SSMS 2005 you need to press the execute button to refresh the results if you are just entering it into the grid there. — Martin Smith, Jan 01 '11 at 20:50
@ OMG Ponies I create table from Management Studio, I check is identity to yes in studio for id column. — Damir, Jan 01 '11 at 20:59
what do you mean by "I try to insert it" ? You should not insert the ID itself, it just gets added to your record. Eyal — epeleg, Jan 01 '11 at 20:59
@ epeleg I insert in code with Linq to SQL, and I don't put any value for id . I tried after that directly from Man. Stud. I don't put any value in ID but it doesn't work. — Damir, Jan 01 '11 at 21:11

OMG Ponies · Answer 1 · 2011-01-02T03:17:26.033

4

Based on the CREATE TABLE statement provided, it's obvious that the IDENTITY property was not enabled. It should've resembled:

CREATE TABLE [dbo].[club](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [name] [varchar](50) NOT NULL,
  [id_city] [int] NOT NULL,
  [street] [varchar](50) NULL,
  [street_number] [nchar](10) NULL,
  [descritpion] [varchar](500) NULL,
  [logo_path] [varchar](50) NULL,
  CONSTRAINT [PK_club] PRIMARY KEY CLUSTERED (
    [id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  )

IDENTITY can not be applied after the table has been created -- the table needs to be dropped & created. You have a couple of options:

drop the table, use the CREATE TABLE statement I provided, re-add data to the table
create a temp table to store the data in, drop the existing club table, re-create the club table with the IDENTITY property, import rows from the temp table, carry on inserting additional data

edited Jan 02 '11 at 03:17

answered Jan 01 '11 at 21:18

OMG Ponies

325,700
82
523
502

please show how you apply the IDENTITY property to an existing column. I think dropping the column and adding a new one or recreating the whole table is unavoidable – bernd_k Jan 01 '11 at 21:29
1

@bernd_k - That is correct. I've no idea why as presumably it's just a bit of a metadata somewhere and it doesn't affect the physical storage of the data. [Some discussion here on the issue](http://stackoverflow.com/questions/288222/how-do-i-add-the-identity-property-to-an-existing-column-in-sql-server) – Martin Smith Jan 01 '11 at 21:35
1

@bernd_k - For SQL Server 2008 it turns out that this *is* possible [see my answer here](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss/6086661#6086661). – Martin Smith May 22 '11 at 16:59

score 1 · Accepted Answer · answered Jan 01 '11 at 21:20

Since autoincrement doesn't work, you have probably no valuable data.

I propose to recreate your table with the following script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

DROP TABLE [dbo].[club]
GO

CREATE TABLE [dbo].[club](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [id_city] [int] NOT NULL,
    [street] [varchar](50) NULL,
    [street_number] [nchar](10) NULL,
    [descritpion] [varchar](500) NULL,
    [logo_path] [varchar](50) NULL,
 CONSTRAINT [PK_club] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

score 0 · Answer 3 · answered Jan 01 '11 at 21:03

0

A Primary Key does not increment by itself because it's a primary key.

If you want an 'auto increment' field in SQL Server, the column has to be set as an identity column.

answered Jan 01 '11 at 21:03

Frederik Gheysels

56,135
11
101
154

Primary key doesn't increment

3 Answers3