[SOLVED] Move existing Foreign Key to different file group

Move existing Foreign Key to different file group

I used the following code

ALTER TABLE Table_name 
DROP CONSTRAINT constraint_name

ALTER TABLE Table_name 
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name) REFERENCES ref_table (ref_column)
ON [filegroup_name]

But I got this error

Incorrect syntax near 'filegroup_name'

Solution

Constraint is nothing but the rule made on the table.So create table in the FileGroup you wish.
Primary keys you may need to mention[filegroup name] along with the syntax since by default CLustered index will get created. [Just in case if you want your index pages in different filegroup instead of Primary filegroup]
Hence there is no need to specify ON [FileGroupName] while adding the foreign key constraint.

Hence go with:
ALTER TABLE Table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name) REFERENCES ref_table (ref_column)