Having my primary key field autoincrement when new details are added

  • I want my Primary Key "ProductNo" autoincrement when new details are added to my table but Im not sure of the SQL to set this up. The below code gives me an error on the AUTOINCREMENT line. What should the correct Syntax be??

    CREATE TABLE `product` (

    `Producttype` varchar(4) collate latin1_general_ci default NULL,

    `ProductName` varchar(80) collate latin1_general_ci default NULL,

    `ProductNo` decimal(50,0) NOT NULL DEFAULT AUTOINCREMENT,

    `Stockamount` decimal(5,0) default NULL,

    `Display` decimal(3,0) default NULL,

    `Description` varchar(1000) collate latin1_general_ci default NULL,

    `Price` decimal(6,2) default NULL,

    `Image` varchar(50) collate latin1_general_ci default NULL,

    `Imgae2` varchar(50) collate latin1_general_ci default NULL,

    PRIMARY KEY (`ProductNo`)

    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

  • set identity on your primary key field. If you want number to start with 1 and increment by 1 at every insert then in your case it should be:

    ProductNo decimal(50,0) primary key identity (1,1)

    You may set identity while creating table or using design view thru EM or SQLMS.

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • Getting an error

    mysql> CREATE TABLE `product` (

    `Producttype` varchar(4) collate latin1_general_ci default NULL,

    `ProductName` varchar(80) collate latin1_general_ci default NULL,

    `ProductNo` decimal(50,0) primary key identity (1,1),

    `Stockamount` decimal(5,0) default NULL,

    `Display` decimal(3,0) default NULL,

    `Description` varchar(1000) collate latin1_general_ci default NULL,

    `Price` decimal(6,2) default NULL,

    `Image` varchar(50) collate latin1_general_ci default NULL,

    `Imgae2` varchar(50) collate latin1_general_ci default NULL,

    PRIMARY KEY (`ProductNo`)

    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

    ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identity (1,1),

    `Stockamount` decimal(5,0) default NULL,

    `Display` decimal' at line 4

  • This is an SQL Server forum... and SQL is not SQL between RDBMS's... if you want MySQL help, I suggest you find a MySQL forum or refer to the documentation for MySQL at MySQL.com.

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

  • I am not sure what it would be in case of MYSQL. I thought you are asking in SQL Server.

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • I was just looking for the SQL syntax to create the table format

  • Sure... understood... but you want to create an auto-increment column, as well as creating a table. That's not the same between RDBMS's... for example, Oracle doesn't even allow for an auto-increment column... you have to use a function. Obviously, SQL Server's auto-increment is different than My SQL because you got an error. Only thing left for me to do to answer your question is to look it up in the documentation... but you can do that, too. 😀

    --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 7 posts - 1 through 6 (of 6 total)

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