CODEWITHSUNDEEP
sqlsql-servert-sqlidentity
Damir
Damir

Reputation: 56249

Primary key doesn't increment

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

Upvotes: 1

Views: 3483

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332791

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

Upvotes: 4

bernd_k
bernd_k

Reputation: 11966

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

Upvotes: 1

Frederik Gheysels
Frederik Gheysels

Reputation: 56984

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.

Upvotes: 0

Related Questions