On which filegroup will constraints be created by default when adding them to a table on a filegroup and how should they be separeated?

Question

We have multiple filegroups, that look something like this:

1. PRIMARY (default)
2. app_data
3. app_index

When I create a table on filegroup app_data

CREATE TABLE Person
(
 [PersonID] INT NOT NULL IDENTITY,
 [Identifier] NVARCHAR(64) NOT NULL,
 [SupervisorID] INT NULL
) ON app_data

and in the current setup (I haven't made this) constraints will get added in 3 different ways

-- Primary keys are added to app_data
ALTER TABLE Person ADD CONSTRAINT PK_Person
 PRIMARY KEY (PersonID) ON app_data
-- Foreign keys are added to "nothing"
ALTER TABLE Person ADD CONSTRAINT FK_Person_PERSON
 FOREIGN KEY (SupervisorID) REFERENCES Person(PersonID)
-- Unique Indexes are added on app_index
-- Sometimes also with constraint notation (but these are the same in background anyway)
CREATE INDEX UX_Person_Identifier
 ON Person(PersonID) ON app_index

On what filegroup will the foreign key be created? app_data or PRIMARY?

Should all constraints be created on app_index? Or should only "custom" indexes be created on this filegroup?

Answer 1

Only PRIMARY KEY and UNIQUE constraints have an optional ON filegroup clause. That's because these constraints always create an automatic index to support their uniqueness. The filegroup clause decides where this index will live, so in your case it should always be 'app_index'.

You cannot specify a filegroup for other constraints such as FOREIGN KEY, CHECK or DEFAULT.

The metadata about all constraints (their name, table, columns etc.) is kept in system tables and thus is always in the PRIMARY filegroup (even if you change the default filegroup).

BTW: That's why you always have to restore at least the PRIMARY filegroup of a database. Without it, there is no metadata about the database content available to SQL Server. And this is why I recommend to use the PRIMARY filegroup only for metadata - at least in enterprise scenarios. Have at least one other filegroup and set that as default. This way PRIMARY remains the minimal possible size and runs the least risk of corruption.