How to fix Error 3867 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 3867 – FileTable System Defined Constraints Cannot Be Modified

Why This Error Happens

This error occurs because SQL Server automatically creates system-defined constraints (such as unique constraints on stream_id, path_locator, and name) when the FILETABLE_NAMESPACE option is enabled on a FileTable. These system constraints are locked and cannot be dropped, altered, or recreated while the table is actively functioning as a FileTable, since doing so would compromise the integrity of the Windows-compatible directory and file namespace that SQL Server maintains internally. Attempting to run an ALTER TABLE statement that touches these constraints—even indirectly through a script that rebuilds all constraints—triggers Error 3867 because only user-defined constraints are permitted to be modified in this state.

Step-by-Step Resolution Guide

Step 1: Identify the Constraint Causing the Conflict

  • Run the following query to list all constraints on the FileTable and determine which ones are system-generated:
SELECT name, type_desc, is_system_named 
FROM sys.objects 
WHERE parent_object_id = OBJECT_ID('YourFileTableName') 
AND type IN ('UQ','PK','C');
  • Confirm whether the constraint you are trying to modify is user-defined or system-defined (system-defined names typically start with FileTableUniqueConstraint or FileTablePrimaryKeyConstraint).

Step 2: Temporarily Disable the FILETABLE_NAMESPACE Option

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Execute the following command to disable the namespace, which unlocks the system constraints for modification:
ALTER TABLE YourFileTableName SET (FILETABLE_NAMESPACE = OFF);
  • Verify no active file handles are open via Windows Explorer or the FileTable share path (e.g., \\ServerName\MSSQLSERVER\YourFileTableName) before proceeding, as open handles will block this operation.

Step 3: Apply Your Schema Changes

  • With the namespace disabled, make your required modifications strictly to user-defined constraints only (do not attempt to alter the system constraints even now, as this will corrupt the FileTable structure).
  • If your original intent was to modify system behavior (e.g., path length or collation), note that this requires dropping and recreating the FileTable entirely rather than altering constraints.

Step 4: Re-enable the FILETABLE_NAMESPACE Option

  • Once your changes are complete, restore full FileTable functionality by running:
ALTER TABLE YourFileTableName SET (FILETABLE_NAMESPACE = ON);
  • Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm the namespace re-enabled successfully without secondary errors.

Alternative or Verification Steps

  • Verification: Query sys.filetables and confirm the is_enabled column returns 1, then test file upload/retrieval via the FileTable’s UNC share path to ensure Windows API access is fully restored.
  • Alternative: If constraints need permanent structural changes (not just toggling), export the file data, drop the FileTable entirely using DROP TABLE, and recreate it with the corrected schema definition from scratch, then re-import the files.