CODEWITHSUNDEEP
sqlsql-server
null
null

Reputation: 59

Primary key isn't incremented automatically

I tried to modify the PK in my table to change the data type of the key from int to nvarchar. I changed it back to int but I lost some of the key's properties.

The primary key was auto increment but now when I am trying to add a new record without assigning a specific number to the PK, it sets the key to null value which prevent the record from being inserted.

enter image description here

Is there any way I can set the key to auto increment again?

enter image description here

I am using SQL server. I would appreciate your help.

Upvotes: 2

Views: 1614

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5643

Open the table in the design mode then select the column and enable the identity property as shown in the image.

seed

Is the value that is used for the very first row loaded into the table.

increment

Is the incremental value that is added to the identity value of the previous row that was loaded.

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Note: If your table contains data already so the value of seed would be the max value of that column + 1.

enter image description here

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 12959

DriverId is the first column in the table. You cannot ALTER Column and make it as identity.

You have two choices.

1. If you want DriverId to remain as first column in the table

  1. Create new table with same schema as existing table
CREATE TABLE dbo.Drivers_new
(
DriverId INT IDENTITY(1,1) NOT NULL,
firstName ...
lastName ...
.
.
)
  1. Load data from existing table to new table 
SET IDENTITY_INSERT ON dbo.Drivers_new

INSERT INTO dbo.Drivers_new(DriverId,firstName,lastName...)
SELECT DriverId, firstName, lastName ...
FROM dbo.Drivers

SET IDENTITY_INSERT OFF dbo.Drivers_new
  1. Rename old table to backup table and new table as old table
sp_rename 'dbo.Drivers', 'Drivers_old'
sp_rename 'dbo.Drivers_new', 'Drivers'

2.If you are fine to have DriverId as another column in the end

1.Drop current column and recreate with identity. Now the DriverId column will be added in the end

ALTER TABLE dbo.Drivers DROP COLUMN DriverId
ALTER TABLE dbo.Drivers ADD DriverId INT IDENTITY(1,1) NOT NULL

Upvotes: 1

Amin Golmahalleh
Amin Golmahalleh

Reputation: 4196

You need to set the identity to that column again.

First Remove Constraint:

ALTER TABLE dbo.Driver
DROP CONSTRAINT PK_Driver;

follow a photo to find the name of the Constraint:

enter image description here

then remove primary key column:

ALTER TABLE dbo.Driver 
DROP COLUMN DriverId;

finally Add Primary key column again:

ALTER TABLE dbo.Driver
   add DriverId INT IDENTITY(1,1)
       CONSTRAINT PK_Driver PRIMARY KEY CLUSTERED;

Upvotes: 2

Related Questions