March 20, 2008 at 5:24 pm
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;
March 20, 2008 at 5:37 pm
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]
March 20, 2008 at 5:59 pm
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
March 20, 2008 at 8:20 pm
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
Change is inevitable... Change for the better is not.
March 20, 2008 at 10:42 pm
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]
March 21, 2008 at 4:25 am
I was just looking for the SQL syntax to create the table format
March 21, 2008 at 9:18 am
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply