Trouble in insertion : - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trouble in insertion :

Author

Topic

echidna
Starting Member

0 Posts

Posted - 2012-01-14 : 03:11:07

Hi All

I am facing problem in insertion

insert into Purchasing.PurchaseOrderHeader(PurchaseOrderID,RevisionNumber,
Status,EmployeeID,VendorID,ShipMethodID,SubTotal,Freight)
values(0,3,244,83,4,507.12
,17.99,5.026)

Note: PurchaseOrderID is an identity column

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_PurchaseOrderHeader_Status". The conflict occurred in database "AdventureWorks2005", table "Purchasing.PurchaseOrderHeader", column 'Status'.
The statement has been terminated.

I have checked the constraints on this table and that is below:

ALTER TABLE [Purchasing].[PurchaseOrderHeader] WITH CHECK ADD CONSTRAINT [CK_PurchaseOrderHeader_Status] CHECK (([Status]>=(1) AND [Status]<=(4)))
GO
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_Status]
GO

I have inserted the value 4 for the column Status in my table eventhough it is throwing me error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 04:27:57

as per your insert you're passing value 244 for status which is why it failed. I think you need to change order of columns in insert to make sure correct value is send for Status (ie 4).
if purchaseorderid is identity type then you dont need to include it in column list and also pass any value for it. It will be generated automatically by SQL Server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-01-14 : 04:38:00

Thank you very much .It worked after changing order of value..Thanks lot.

insert into Purchasing.PurchaseOrderHeader(RevisionNumber,Status,EmployeeID,
VendorID,ShipMethodID,SubTotal,TaxAmt,Freight)
values(0,4,244,83,3,507.12
,17.99,5.026)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-14 : 04:54:07

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources