topic Re: 'ApplyEdits' with one 'adds' fail when SQL server table have a FOREIGN KEY in ArcGIS REST APIs and Services Questions

'ApplyEdits' with one 'adds' fail when SQL server table have a FOREIGN KEY

GertConradie — Wed, 07 Jan 2015 12:16:29 GMT

I have a REST (FeatureServer) service that I make an 'ApplyEdits' call to. It only succeeds if I remove an existing FK constraint on one of my columns.

When the FK is applied, the service request fail and supply a code 1060 and a "Rowbuffer creation failed." error. (In the logs available in the ArcGIS server administrator it becomes more clear what the internal SQL error was.)

Is there a work-around for that? Would be much appreciated.

Full scripts for my SQL tables attached.

Small snippet below:

========================

--=====================================
-- Unless this FK constraint is removed, an ESRI Feature Service 'ApplyEdits' with the following 'adds' fail:
-- [{"geometry":{"x":97655.99152462519,"y":367271.9541849444,"spatialReference":{"wkid":29903,"latestWkid":29903}},"attributes":{"Label":"blablabla","SpecificFeatureTypeId":2,"PlanId":1}}]
--=====================================
ALTER TABLE [dbo].[PlanSpecificFeatures] WITH CHECK ADD CONSTRAINT [FK_PlanSpecificFeatures_SpecificFeatureType] FOREIGN KEY([SpecificFeatureTypeId])
REFERENCES [dbo].[SpecificFeatureType] ([SpecificFeatureTypeId])
GO

ALTER TABLE [dbo].[PlanSpecificFeatures] CHECK CONSTRAINT [FK_PlanSpecificFeatures_SpecificFeatureType]
GO

Re: 'ApplyEdits' with one 'adds' fail when SQL server table have a FOREIGN KEY

GertConradie — Wed, 07 Jan 2015 12:19:50 GMT

Note that the integer value that I use in my request is a valid value that satisfy normal TSQL insert scripts when the FK is applied.

Re: 'ApplyEdits' with one 'adds' fail when SQL server table have a FOREIGN KEY

GertConradie — Mon, 12 Jan 2015 09:55:46 GMT

I got the fix/workaround:

You need to set the identity seed value of the 'master' table that you have a relationship with to start with '0' instead of the default '1'

I assume ArcGIS first create a record with the default int/string values and then update the attributes afterwards. (Might be problematic on a database with lots of integrity checks applied...)

CREATE TABLE [dbo].[SpecificFeatureType](
[SpecificFeatureTypeId] [int] IDENTITY(0,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_SpecificFeatureType] PRIMARY KEY CLUSTERED
(
[SpecificFeatureTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]