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