SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Amazon Web Services AWS Tutorials and Guides
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Amazon Redshift Database Error: seed value for an identity out of range


While migrating database tables from SQL Server to Amazon Redshift database, during table creation on Redshift database I came across with following interesting error regarding identity column seed value.
The error message is as follos: [Amazon](500310) Invalid operation: seed value for an identity out of range; [SQL State=22023, DB Errorcode=500310]

I used AWS Schema Conversion Tool (SCT) to prepare create table command SQL scripts to migrate SQL Server database table objects to Amazon Redshift database.
In one of those tables, the source Create Table SQL command on SQL Server and the converted DDL command for Amazon Redshift database is as follows:

-- from source schema on SQL Server database
CREATE TABLE [OFSA].[ServiceOrderNumber](
[ID_ServiceOrderNumber] int IDENTITY(-2147483648, 1) NOT NULL,
[ServiceOrderNumber] bigint NULL
)
ON [PRIMARY];
-- and for target schema on Amazon Redshift database
CREATE TABLE IF NOT EXISTS ofsa.serviceordernumber
(
id_serviceordernumber INTEGER IDENTITY(-2147483648, 1) NOT NULL,
serviceordernumber BIGINT
)
DISTSTYLE KEY
DISTKEY
(
id_serviceordernumber
)
SORTKEY
(
serviceordernumber
);

Of course as SQL developers can guess, executing the resultant SQL script after conversion by SCT failed.

AWS Schema Conversion Tool Create Table DDL SQL scripts

The execution of the above SQL script on Amazon Redshift database will throw below exception

[Amazon](500310) Invalid operation: seed value for an identity out of range; [SQL State=22023, DB Errorcode=500310]
1 statement failed.

To correct this error on Amazon Redshift database, changing the initial SEED value from -2147483648 [Add to Citavi project by ISBN] to -2147483647 for IDENTITY column with INT data type.

It is interesting that although INT data type has a valid value range between -2147483648 to +2147483647 above SQL error occurs for INT data type while with its -32768 to +32767 valid value range SMALLINT data type will not cause an SQL error when -32768 is used as the initial SEED value.





AWS Tutorials

AWS Tutorials

AWS








Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems