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_RecipDemotb
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 NOT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[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 NULL ,
[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 NOT 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 NOT 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_RecipDemotb
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 NOT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[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 NULL ,
[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 NOT 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 NOT 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:
>
>