Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem'
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem'
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>