Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filegroups Expand / Collapse
Author
Message
Posted Sunday, December 06, 2009 6:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 AM
Points: 7, 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.
Post #829585
Posted Sunday, December 06, 2009 7:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 01, 2013 12:49 PM
Points: 44, Visits: 302
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.
Post #829593
Posted Sunday, December 06, 2009 9:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 AM
Points: 7, 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?
Post #829623
Posted Sunday, December 06, 2009 9:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:28 AM
Points: 32,818, Visits: 14,963
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
Post #829629
Posted Sunday, December 06, 2009 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 1:10 AM
Points: 7, Visits: 39
thanks steve!
Post #829650
Posted Monday, December 07, 2009 7:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:25 PM
Points: 1,159, Visits: 3,067
I think you can try to

create clustered index with DROP_EXISTING clause.

Therefore you can proceed w/o drop FK.
Post #829841
Posted Sunday, March 17, 2013 5:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 06, 2013 11:01 AM
Points: 2, Visits: 14
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.
Post #1432048
Posted Monday, March 18, 2013 2:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:11 AM
Points: 41,557, Visits: 34,477
Please note: 3 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1432098
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse