Error with IDENTITY property on primary key column

Question

Post reply

Error with IDENTITY property on primary key column

michael.leach2015

SSCommitted

Points: 1597
More actions
August 29, 2019 at 3:33 am

#3675290
I just can't figure out what is wrong with this code:
```
CREATE TABLE SubEmployee
(
    LineNo          smallint        IDENTITY (1,1)      NOT NULL    CONSTRAINT PK_LineNo PRIMARY KEY CLUSTERED (LineNo asc),
    SubEmployeeID   char(5)         NOT NULL,
    SubEmployeeName varchar(30)     NOT NULL
);
```
The error message is:
```
Msg 156, Level 15, State 1, Line 51

Incorrect syntax near the keyword 'LineNo'.
```
I tried using 'int' instead of 'smallint' but that didn't work. I flipped around the NOT NULL and the IDENTITY (1,1) part and that didn't work. What is wrong with my code?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

LineNo is a reserved word in T-SQL. don't use it as a column name.

Gerard Wolfaardt SSC Enthusiast Points: 196 More actions · Answer 2

LineNo is a SQL reserved word, so I would suggest you find another name. If you however have no other choice, wrap it in square brackets which will do the job:

CREATE TABLE SubEmployee
(
   [LineNo] SMALLINT IDENTITY (1, 1) NOT NULL CONSTRAINT PK_LineNo PRIMARY KEY CLUSTERED ([LineNo] ASC),
   [SubEmployeeID] CHAR(5) NOT NULL,
   [SubEmployeeName] VARCHAR(30) NOT NULL
);

This reply was modified 5 years, 3 months ago by Gerard Wolfaardt.
This reply was modified 5 years, 3 months ago by Gerard Wolfaardt.
This reply was modified 5 years, 3 months ago by Gerard Wolfaardt.

DesNorton SSC-Insane Points: 24399 More actions · Answer 3

The answer to this SO question explains why it is a reserved word

https://stackoverflow.com/questions/4054511/what-exactly-does-the-t-sql-lineno-reserved-word-do

How to post data/code on a forum to get the best help.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

I hope you understand that the old Sybase IDENTITY table property (property -- it is not a column) can never by definition be a valid key. It is a count of physical insertion attempts to a physical file on one physical disk on one physical machine. It has no place in the correct logical model. But people who are learning SQL and RDBMS feel much more comfortable with the physical record counts that there used to from filesystems and mag tapes.

Since the table models said, its name should be a collective or plural noun. Unless you have only one "sub_employee", as shown by the singular name your model is wrong. But a better question is, "what do you mean by sub_employee? Why are they totally and completely absolutely different kinds of things that a mere employee? My guess is that being a sub employee is some kind of status or type in your data model. Then you talk about a line number as an attribute of the employee. Not of the file. Not of a paper form. Not of a display screen. I didn't know that employees came with line numbers 🙁 I think it's pretty obvious you're messing logical and physical modeling the same table; this is a very common beginner's mistake my guess is that your table should look something like this:

CREATE TABLE Personnel

(employee_id CHAR(5) NOT NULL PRIMARY KEY,

employee_type CHAR(1) NOT NULL

CHECK (employee_type IN (???),,

employee_name VARCHAR(30) NOT NULL); -- USPS size!

Please post DDL and follow ANSI/ISO standards when asking for help.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 5

Gerard,

I forgot to consider the possibility of reserved words. Thank you.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

jcelko212 32090 wrote:

I hope you understand that the old Sybase IDENTITY table property (property -- it is not a column) can never by definition be a valid key. It is a count of physical insertion attempts to a physical file on one physical disk on one physical machine. It has no place in the correct logical model.

While I agree that adding an IDENTITY column to every table is an improper use, I hope that, someday, you'll understand just how wrong your assertion actually is.

And, no, it doesn't have to be limited "to a physical file on one physical disk on one physical machine" if you know anything about partitioning, file groups, and files.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

jcelko212 32090 wrote:

my guess is that your table should look something like this:
CREATE TABLE Personnel
(employee_id CHAR(5) NOT NULL PRIMARY KEY,
employee_type CHAR(1) NOT NULL
CHECK (employee_type IN (???),,
employee_name VARCHAR(30) NOT NULL); -- USPS size!

Actually, the USPS "optimal" standard is 64 bytes in order "to be compatible with the Postal Service National ZIP+4 database". Of course, that doesn't include anything for countries outside of the U.S. and they can be pretty darned long without violating the purpose of each line in an address. https://pe.usps.com/text/pub28/28c2_001.htm

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)