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

Question

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:

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

@tadman: Yes, the local db that comes with Visual Studio 2019 — B. Clay Shannon-B. Crow Raven, Jun 16 '20 at 23:23
Did you create that table or was it there? Does the table contain data? Any other table which depends on the data (id column) of this table ? Do you have management studio? — Srinika Pinnaduwage, Jun 17 '20 at 01:59
Did you drop the primary key constraint on the column before trying to drop it or change its type? — AlwaysLearning, Jun 17 '20 at 03:07
@AlwaysLearning - Yes, and it threw an exception then, too. I'm just going to create another table, sans ID, and use that. I don't need no stinkin' ID, as the Bandolero in "Treasure of the Sierra Madre" said (or something like that). — B. Clay Shannon-B. Crow Raven, Jun 17 '20 at 03:42
@SrinikaPinnaduwage: I have Server Explorer; I created the table. I created the ID out of habit, and assumed it would be an AutoInc situation, but it's not. — B. Clay Shannon-B. Crow Raven, Jun 17 '20 at 03:43
There are probably a chain of constraints that need to be dropped: if you had a primary key constraint on dbo.WordsToIgnore.Id then you probably have foreign key constraints referencing that. A new table won't fix that - you'll have to drop the FK constraints and recreate them referencing the new table. — AlwaysLearning, Jun 17 '20 at 03:46

score 1 · Accepted Answer · answered Jun 17 '20 at 04:42

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

score 0 · Answer 2 · answered Jun 17 '20 at 03:58

0

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"

answered Jun 17 '20 at 03:58

B. Clay Shannon-B. Crow Raven

8,547
144
472
862

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

2 Answers2