SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filegroups


Filegroups

Author
Message
darwin.maramot
darwin.maramot
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 39
Hi guys

I have a problem, i'd created a new file group and i wanted to transfer some tables to the new one. all you need to do is transfer the clustered index right? so i did it. seems that i cannot transfer a table to my newly created file group. error shows that i cannot drop my primary key because it is being referenced by a foreign key constraint. am really clueless right now...

could you help me out... thanks!!!



this is my script.

USE [Virtuabanker]
GO

/****** Object: Index [PK_tbl_audit_master_1__13] Script Date: 12/07/2009 09:01:38 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_audit_master]') AND name = N'PK_tbl_audit_master_1__13')
ALTER TABLE [dbo].[tbl_audit_master] DROP CONSTRAINT [PK_tbl_audit_master_1__13]
GO

USE [Virtuabanker]
GO

/****** Object: Index [PK_tbl_audit_master_1__13] Script Date: 12/07/2009 09:01:38 ******/
ALTER TABLE [dbo].[tbl_audit_master] ADD CONSTRAINT [PK_tbl_audit_master_1__13] PRIMARY KEY CLUSTERED
(
[log_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [SECONDARY]
GO

this is the error result

Msg 3725, Level 16, State 0, Line 4
The constraint 'PK_tbl_audit_master_1__13' is being referenced by table 'tblc_audit_acct_grp', foreign key constraint 'FK_tblc_audit_acct_grp_tbl_audit_master'.
Msg 3727, Level 16, State 0, Line 4
Could not drop constraint. See previous errors.
Msg 1779, Level 16, State 0, Line 3
Table 'tbl_audit_master' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

Tristan Chiappisi
Tristan Chiappisi
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 331
It appears that the 'tblc_audit_acct_grp' table has a foreign key reference to the tbl_audit_master table. You'll need to drop the foreign key reference from the tblc_audit_acct_grp (FK_tblc_audit_acct_grp_tbl_audit_master) and then rerun the statement.
darwin.maramot
darwin.maramot
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 39
Tristan Chiappisi (12/6/2009)
It appears that the 'tblc_audit_acct_grp' table has a foreign key reference to the tbl_audit_master table. You'll need to drop the foreign key reference from the tblc_audit_acct_grp (FK_tblc_audit_acct_grp_tbl_audit_master) and then rerun the statement.


thanks tristan, what would happen to the table that has the foreign key (tblc_audit_acct_grp), will the foreign key constrain rebuild itself after i transfer the table to the new filegroup?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)

Group: Administrators
Points: 251342 Visits: 19818
No, you will need to manually rebuild the FK constraint

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
darwin.maramot
darwin.maramot
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 39
thanks steve!
Pei Zhu-415513
Pei Zhu-415513
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2192 Visits: 4299
I think you can try to

create clustered index with DROP_EXISTING clause.

Therefore you can proceed w/o drop FK.
Fernando Franco
Fernando Franco
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 17
hi , have you tried with this command ?

ALTER TABLE [tbl_audit_master]
DROP CONSTRAINT [PK_tbl_audit_master_1__13] WITH (MOVE TO SECONDARY)

then

ALTER TABLE [tbl_audit_master]
ADD CONSTRAINT [PK_tbl_audit_master_1__13] PRIMARY KEY CLUSTERED
( [log_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [SECONDARY]


Best regards ,
Fernando Franco.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)SSC Guru (417K reputation)

Group: General Forum Members
Points: 417858 Visits: 47141
Please note: 3 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search