none
How to create an auto increment primary key

    Question

  • I have created a table using SQL Server Enterprise Manager, but I have no idea how to create / modify a field to become an auto increment primary key with command way or SQL server enterprise manager way. Any idea?
    Thursday, February 25, 2010 9:15 AM

Answers

  • CREATE TABLE [dbo].[x](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[text] [nchar](10) NOT NULL,
     CONSTRAINT [PK_x] 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
    insert into x values ('one') go insert into x values ('two') 
    

    My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010
    Thursday, February 25, 2010 9:20 AM
  • AUTO_INCREMENT is not valid TSQL on Sql Server
    Replace it with

    IDENTITY
    
    
    (1,1)
    
    Try the whole thing as :
    CREATE TABLE staffsaleorder2
    (
    orderID int IDENTITY(1,1) not null,
    badgeno varchar(10),
    itemcode varchar(10),
    orderquantity int,
    price decimal(9),
    itemdescription varchar(100),
    time timestamp(8),
    CONSTRAINT [PK_staffsaleorder2] PRIMARY KEY CLUSTERED 
    (
    	[orderID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
     
    

    HTH Ciaran http://wannabedeveloper.spaces.live.com
    Thursday, February 25, 2010 9:59 AM
  • Sql Server doesn't have AUTO_INCREMENT keyword. you should use IDENTITY Property instead which is described @ Here


    My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010
    Thursday, February 25, 2010 10:00 AM
  • AUTO_INCREMENT is not the correct syntax. If you look at Mr. Help's example the syntax is IDENTITY(1,1)

    Matt

    Thursday, February 25, 2010 10:06 AM
  • Yes of course
    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Tmp_x
    	(
    	id int NOT NULL IDENTITY (1, 1),
    	text nchar(10) NOT NULL
    	)  ON [PRIMARY]
    GO
    ALTER TABLE dbo.Tmp_x SET (LOCK_ESCALATION = TABLE)
    GO
    SET IDENTITY_INSERT dbo.Tmp_x ON
    GO
    IF EXISTS(SELECT * FROM dbo.x)
    	 EXEC('INSERT INTO dbo.Tmp_x (id, text)
    		SELECT id, text FROM dbo.x WITH (HOLDLOCK TABLOCKX)')
    GO
    SET IDENTITY_INSERT dbo.Tmp_x OFF
    GO
    DROP TABLE dbo.x
    GO
    EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT' 
    GO
    ALTER TABLE dbo.x ADD CONSTRAINT
    	PK_x PRIMARY KEY CLUSTERED 
    	(
    	id
    	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    GO
    COMMIT
    

    My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010
    Friday, February 26, 2010 10:04 AM

All replies

  • CREATE TABLE [dbo].[x](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[text] [nchar](10) NOT NULL,
     CONSTRAINT [PK_x] 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
    insert into x values ('one') go insert into x values ('two') 
    

    My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010
    Thursday, February 25, 2010 9:20 AM
  • What's wrong!?

    CREATE TABLE staffsaleorder2
    (
    orderID int not null AUTO_INCREMENT,
    badgeno varchar(10),
    itemcode varchar(10),
    orderquantity int,
    price decimal(9),
    itemdescription varchar(100),
    time timestamp(8)
    PRIMARY KEY (orderID)
    )

    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near 'AUTO_INCREMENT'.

    I have no idea!
    Thursday, February 25, 2010 9:52 AM
  • AUTO_INCREMENT is not valid TSQL on Sql Server
    Replace it with

    IDENTITY
    
    
    (1,1)
    
    Try the whole thing as :
    CREATE TABLE staffsaleorder2
    (
    orderID int IDENTITY(1,1) not null,
    badgeno varchar(10),
    itemcode varchar(10),
    orderquantity int,
    price decimal(9),
    itemdescription varchar(100),
    time timestamp(8),
    CONSTRAINT [PK_staffsaleorder2] PRIMARY KEY CLUSTERED 
    (
    	[orderID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
     
    

    HTH Ciaran http://wannabedeveloper.spaces.live.com
    Thursday, February 25, 2010 9:59 AM
  • Sql Server doesn't have AUTO_INCREMENT keyword. you should use IDENTITY Property instead which is described @ Here


    My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010
    Thursday, February 25, 2010 10:00 AM
  • AUTO_INCREMENT is not the correct syntax. If you look at Mr. Help's example the syntax is IDENTITY(1,1)

    Matt

    Thursday, February 25, 2010 10:06 AM
  • AUTO_INCREMENT is not the correct syntax. If you look at Mr. Help's example the syntax is IDENTITY(1,1)

    Matt


    I see, is there a way to do that through keyword like "modify" instead of making anew table? I always have a table.
    Friday, February 26, 2010 1:28 AM
  • Yes of course
    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    BEGIN TRANSACTION
    GO
    CREATE TABLE dbo.Tmp_x
    	(
    	id int NOT NULL IDENTITY (1, 1),
    	text nchar(10) NOT NULL
    	)  ON [PRIMARY]
    GO
    ALTER TABLE dbo.Tmp_x SET (LOCK_ESCALATION = TABLE)
    GO
    SET IDENTITY_INSERT dbo.Tmp_x ON
    GO
    IF EXISTS(SELECT * FROM dbo.x)
    	 EXEC('INSERT INTO dbo.Tmp_x (id, text)
    		SELECT id, text FROM dbo.x WITH (HOLDLOCK TABLOCKX)')
    GO
    SET IDENTITY_INSERT dbo.Tmp_x OFF
    GO
    DROP TABLE dbo.x
    GO
    EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT' 
    GO
    ALTER TABLE dbo.x ADD CONSTRAINT
    	PK_x PRIMARY KEY CLUSTERED 
    	(
    	id
    	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    GO
    COMMIT
    

    My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010
    Friday, February 26, 2010 10:04 AM