Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.