When creating a custom List in SharePoint, whether through the SharePoint GUI or using the CData driver, several default columns are added automatically. One of these is the “ID” column, which is the primary key for the List.

Although the “ID” column is marked as read-only in the column metadata, SharePoint does not consistently enforce this restriction during data insertion. The actual behavior depends on whether you are using the REST or SOAP schema.

REST:

When inserting records using the REST schema, the “ID” column can be included in the insert statement:

INSERT INTO MyCustomList (ID, Column1) VALUES (1234, 'Hello REST');

In this case, all data in the statement, including the explicitly set “ID” value, is persisted in SharePoint. While the CData driver typically raises an error such as:

Column kColumnName] could not be inserted. This column is read-only.

This restriction is not enforced for the “ID” column when using the REST schema, as of driver version 24.0.9077.0 and later. This exception is specific to the “ID” column because the SharePoint REST API honors and stores the custom “ID” value, even though it is marked as read-only.

Important: This behavior does not apply to other read-only columns. For all other columns marked as read-only, the driver will continue to enforce the constraint and throw the appropriate error if an insert attempts to include them.

SOAP:

The SOAP schema behaves differently. If you include a value for the “ID” column in the insert, the driver will raise the following error:

Column nID] could not be inserted. This column is read-only.

This behavior is intentional. Without this restriction, the SharePoint SOAP API will accept the insert request and return a successful response, but the specified “ID” value will be ignored. SharePoint automatically replaces it with the next available auto-generated value.

For example:

INSERT INTO MyCustomList (ID, Column1) VALUES (1234, 'Hello SOAP');

Even though the API responds with success, the record stored in the List will not have the “ID” value 1234. Instead, SharePoint assigns the next available value from the internal counter. This is considered a false success because the inserted data does not match the intended values. To prevent this inconsistency, the CData driver enforces the read-only constraint for the SOAP API.