Add Foreign Key Constraint in filegroup

Hi,
I'd like to create a foreign key constraint and store it in a different
filegroup than "PRIMARY"
For this, I've created a new filegroup named "INDEX"
This is my script :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
(
[FIL_IDFOLDER]
) REFERENCES [dbo].[T_FOLDER] (
[FOL_IDFOLDER]
) ON DELETE CASCADE
ON [INDEX]
GO
And it doesn't work !
But what is very strange, is that I can create a primary key constraint
correctly on this filegroup :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
(
[FIL_IDFILE]
) ON [INDEX]
GO
And this works !
It would be great if someone can telle me where my error is !
ThanxFilegroups are containers for storing data. Data are either data pages or in
dex pages. The reason
you can specify a filegroup when you define a primary key is that it automat
ically creates an index
for you,. so the file group you specify defines where that index will be sto
red. A foreign key
doesn't come with an index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jerome.avoustin@.gmail.com> wrote in message
news:1137404237.380973.84080@.g43g2000cwa.googlegroups.com...
> Hi,
> I'd like to create a foreign key constraint and store it in a different
> filegroup than "PRIMARY"
> For this, I've created a new filegroup named "INDEX"
> This is my script :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
> (
> [FIL_IDFOLDER]
> ) REFERENCES [dbo].[T_FOLDER] (
> [FOL_IDFOLDER]
> ) ON DELETE CASCADE
> ON [INDEX]
> GO
> And it doesn't work !
> But what is very strange, is that I can create a primary key constraint
> correctly on this filegroup :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
> (
> [FIL_IDFILE]
> ) ON [INDEX]
> GO
> And this works !
> It would be great if someone can telle me where my error is !
> Thanx
>