sqlsql-serversqlclientserver-explorer

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...


Solution

  • 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)
    );