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.
>|||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.
>
>|||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.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id
,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemo
tb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"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.
>
>|||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.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id
,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemo
tb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"Dan Guzman" wrote:

> 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...
>
>|||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)|||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:
>
>|||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:
>
>sql