Re: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 09/09/04
- Next message: Paul: "trying to use variable for text search"
- Previous message: Ron Hinds: "More SQL DB Hangs"
- In reply to: stoko: "Re: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 9 Sep 2004 13:04:00 -0400
It will save you even more time if you simply provide the DDL for BOTH
tables. I cannot help you until you do that.
-- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "stoko" <stoko@discussions.microsoft.com> wrote in message news:F2F57F9B-D3E5-4B47-A53E-4A2F2750E2FB@microsoft.com... Tom, It would have saved us some time if you renamed the tables to DEF. The DDL I sent are production tables. I was trying to change the names, etc, but decided to send you the live information. Thus far, no one has been able to help explain why I cannot delete and recreate constraints in a sp or script or dts on tables that have records. The irony is that this thing worked the first few times and just fails thereafter -- requiring me to recreate the constraints manually. I am still waiting for your support. Thanks. Stoko. "Tom Moreau" wrote: > Actually, we really need the DDL for table dbo.DEF. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinnaclepublishing.com > .. > "stoko" <stoko@discussions.microsoft.com> wrote in message > news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@microsoft.com... > Below is the info you requested. Each time I drop the constraints via sql > analyzer and try recreating them, I have the FK error. I check via EM and > the constraints are not there. What must be going on is beyond my > comprehension. Initially, the first 4 attempts works fine but there-after, > nothing works. Remember that the tables have data. > > Let me know... > Thanks in advance. > Stoko. > > "Tom Moreau" wrote: > > > Post your DDL for table dbo.DEF. > > > > -- > > Tom > > > > --------------------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > > SQL Server MVP > > Columnist, SQL Server Professional > > Toronto, ON Canada > > www.pinnaclepublishing.com/sql > > > > > > "stoko" <anonymous@discussions.microsoft.com> wrote in message > > news:0ba901c48936$784343d0$3501280a@phx.gbl... > > I got the following Error > > > > "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY > > constraint 'FK_ABC_DEF'. The conflict occurred in > > database 'Test', table 'DEF', column 'ID'." > > > > when I ran the following scripts: > > > > if exists (select * from dbo.sysobjects where id = > > object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id, > > N'IsForeignKey') = 1) > > ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF > > GO > > > > > > ALTER TABLE [dbo].[ABC] ADD > > CONSTRAINT [FK_ABC_DEF] FOREIGN KEY > > ( > > [ID] > > ) REFERENCES [dbo].[DEF] ( > > [ID] > > ) ON DELETE CASCADE NOT FOR REPLICATION > > GO > > > > > > My goal was to delete the constraint and recreate it but > > the above error indicates that the FK constraint is still > > active even when I verified on both tables and there were > > not available. > > > > Is this a problem with sqlserver 2000 or the problem is me. > > > > Please help. > > > > > > > >
- Next message: Paul: "trying to use variable for text search"
- Previous message: Ron Hinds: "More SQL DB Hangs"
- In reply to: stoko: "Re: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
