August 29, 2019 at 3:33 am
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?
August 29, 2019 at 5:51 am
LineNo is a reserved word in T-SQL. don't use it as a column name.
August 29, 2019 at 6:20 am
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
);
August 29, 2019 at 7:25 am
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
August 29, 2019 at 9:20 pm
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.
August 30, 2019 at 9:54 pm
Gerard,
I forgot to consider the possibility of reserved words. Thank you.
August 31, 2019 at 2:06 am
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
Change is inevitable... Change for the better is not.
August 31, 2019 at 2:24 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply