CODEWITHSUNDEEP
sqlsql-serversqlclientserver-explorer
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10297

How can I remove the ID column from a local sql table or change it to AUTO INCREMENT?

I want to get rid of the ID column I added to a local sql table.

When I deleted the column from the designer and tried to Update, I got this:

enter image description here

Another option would be to make the ID column AUTO INCREMENT by changing it to "[Id] INT NOT NULL AUTO INCREMENT,", but I also got an error when I added that to the table definition and selected Update.

Even when I change the Table back to what it is (add the ID column back) like so:

CREATE TABLE [dbo].[WordsToIgnore] (
    [Id]           INT        NOT NULL,
    [WordToIgnore] NCHAR (50) NOT NULL,
    [Source]       NCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

...I still get the err msg when I try to Update...

Upvotes: -2

Views: 179

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 13009

We need to define Column as given below. MSDN REFERENCE

<column_definition> ::= column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ SPARSE ]
[ MASKED WITH ( FUNCTION = ' mask_function ') ]
[ CONSTRAINT constraint_name [ DEFAULT constant_expression ] ]
[ IDENTITY [ ( seed,increment ) ]
[ NOT FOR REPLICATION ]
[ GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] ]
[ NULL | NOT NULL ]
[ ROWGUIDCOL ]
[ ENCRYPTED WITH
    ( COLUMN_ENCRYPTION_KEY = key_name ,
      ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED } ,
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) ]
[ <column_constraint> [, ...n ] ]
[ <column_index> ]

So, here it is: 

CREATE TABLE [dbo].[WordsToIgnore] (
    Id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_WordsToIgnore PRIMARY KEY CLUSTERED,
    [WordToIgnore] NCHAR (50) NOT NULL,
    [Source]       NCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Or you can define constraint in a separate line

CREATE TABLE [dbo].[WordsToIgnore] (
    Id INT IDENTITY(1,1) NOT NULL ,
    [WordToIgnore] NCHAR (50) NOT NULL,
    [Source]       NCHAR (50) NOT NULL,
    CONSTRAINT PK_WordsToIgnore PRIMARY KEY CLUSTERED ([Id] ASC)
);

Upvotes: 1

B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10297

Based on this, the following should do the trick:

[Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY

(IOW, intrude the "IDENTITY(1,1)" jazz between "NULL" and "PRIMARY KEY"

Upvotes: 0

Related Questions