I will stay with int, bigint, etc. and will not use decimal. This question is about using decimal if need be. I am just investigating this.
Question: How can I correct the following:
CREATE TABLE MyOrders(orderid decimal(10,4) NOT NULL IDENTITY(0, 0.02) CONSTRAINT PK_MyOrders_orderid PRIMARY KEY, custid INT NOT NULL, empid INT NOT NULL, orderdate DATE NOT NULL);
Error:
Msg 2749, Level 16, State 2, Line 27
Identity column 'orderid' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.
I am running it in SQL Server 2014. Is it deprecated in this version of there is something wrong with the following line:
orderid decimal(10,4) NOT NULL IDENTITY(0, 0.02)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
Scott Pletcher
8/22/2022 - Mon
Ryan Chong
so, it means a decimal primary key must be a decimal with a scale of 0, hence you need to try:
CREATE TABLE MyOrders
(
orderid decimal(10,0) NOT NULL IDENTITY(1,1)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
, custid INT NOT NULL
, empid INT NOT NULL
, orderdate DATE NOT NULL);
or use an int, bigint, smallint, tinyint type instead, like:
orderid Int NOT NULL IDENTITY(1,1)
orderid bigint NOT NULL IDENTITY(1,1)
...
etc
Mike Eghtebas
ASKER
Having decimal(10,4) is a must. Otherwise it is just like int or bigint. I want to have
orderid
1.001
1.002
etc.
Ryan Chong
do you think possible to use a varchar instead? it seems that MS SQL just don't allow a primary key in a zero scaled decimal data type.
i try to find a documentation to support this argument.
Identity column 'orderid' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.
I think this error message is very clear. If you want to use a non integer numeric data type then the scale need to be 0. Isn't a bug but how the things really works and I don't think you can do it in another way.
Mike Eghtebas
ASKER
Could you give me a TSQL code to generate decimal data type identity column?
CREATE TABLE MyOrders
(
orderid decimal(10,0) NOT NULL IDENTITY(1,1)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
, custid INT NOT NULL
, empid INT NOT NULL
, orderdate DATE NOT NULL);
or use an int, bigint, smallint, tinyint type instead, like:
orderid Int NOT NULL IDENTITY(1,1)
orderid bigint NOT NULL IDENTITY(1,1)
...
etc