ALTER TABLE statement conflicted with COLUMN FOREIGN KEY

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.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.|||Perhaps you have existing data that prevents the new constraint from being
created. You can identify this data with the query below:
SELECT *
FROM [dbo].[ABC] AS a
WHERE NOT EXISTS
(
SELECT *
FROM [dbo].[DEF] AS b
WHERE a.[ID] = b.[ID]
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"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.
>|||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.
>
>|||On Wed, 8 Sep 2004 12:15:05 -0700, stoko wrote:
>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.
(snip code)
Hi Stoko,
The code you supplied works fine for me. And when I append the code from
your original post, I get the following error:
Server: Msg 4902, Level 16, State 1, Line 3
Cannot alter table 'dbo.ABC' because this table does not exist in database
'TestDB80'.
Somehow, you seem to have posted the wrong tables here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||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.
> >
> >
> >
>