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 6, 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 6, 2009 7:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 9, 2014 5:02 PM
Points: 44, Visits: 304
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 6, 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 6, 2009 9:35 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:15 PM
Points: 31,210, Visits: 15,654
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 6, 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 7, 2009 7:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 1,221, Visits: 3,234
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 6, 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: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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