December 6, 2009 at 6:09 pm
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.
December 6, 2009 at 7:36 pm
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.
December 6, 2009 at 9:18 pm
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?
December 6, 2009 at 9:35 pm
No, you will need to manually rebuild the FK constraint
December 6, 2009 at 10:52 pm
thanks steve!
December 7, 2009 at 7:21 am
I think you can try to
create clustered index with DROP_EXISTING clause.
Therefore you can proceed w/o drop FK.
March 17, 2013 at 5:54 pm
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.
March 18, 2013 at 2:16 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply