Incorrect syntax in SQL Server query-CodePudding

CREATE TABLE identity (
    empid VARCHAR(255) PRIMARY KEY,
    entry VARCHAR(255)
);

-- Table: vectors
CREATE TABLE vectors (
    f_id   INTEGER PRIMARY KEY IDENTITY(1,1),
    label  STRING  NOT NULL,
    empid  STRING  REFERENCES identity (empid) 
                   NOT NULL,
    vector BLOB    NOT NULL
);

I tried to run the above query but it gives me error

Incorrect syntax near expected '.', ID or QUOTED_ID.

I don't understand why it is giving me this error, is it because IDENTITY is a keyword in SQL Server. Kindly help!

CodePudding user response：

Try this:

CREATE TABLE [identity] (
    empid VARCHAR(255) PRIMARY KEY,
    entry VARCHAR(255)
);


-- Table: vectors
CREATE TABLE vectors (
    f_id   INTEGER PRIMARY KEY IDENTITY(1,1),
    label  VARCHAR(255)  NOT NULL,
    empid  VARCHAR(255)  REFERENCES [identity] (empid) 
                   NOT NULL,
    vector VARCHAR(255)    NOT NULL
);

Also, if you are working with SQL Server Management Studio, you can see that some words are colored in blue. It will be better to avoid them using in your code as names of tables, variables and other objects. For example, identity is such word.