Post reply

Error with IDENTITY property on primary key column

  • 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?

     

     

  • August 29, 2019 at 5:51 am

    #3675301

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

  • August 29, 2019 at 6:20 am

    #3675307

    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

    #3675324

    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.

  • August 29, 2019 at 9:20 pm

    #3675563

    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

    #3675895

    Gerard,

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

  • August 31, 2019 at 2:06 am

    #3675953

    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)

  • August 31, 2019 at 2:24 am

    #3675957

    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)

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

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