CONSTRAINT problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CONSTRAINT problem

Author

Topic

manju3606
Yak Posting Veteran

78 Posts

Posted - 2012-01-16 : 05:43:07

Hi to all ,

I am creating constraint using below query but i am getting error and i am not understanding why ?

so please can any one help me with this

ALTER TABLE [dbo].[History] WITH CHECK ADD CONSTRAINT [FK_History_HotelSummary] FOREIGN KEY([HotelSummaryID])

REFERENCES [dbo].[HotelSummary] ([HotelSummaryID])

GO

ALTER TABLE [dbo].[History] CHECK CONSTRAINT [FK_History_HotelSummary]

--------------------------Error---------------------------------------------------

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_History_HotelSummary". The conflict occurred in database "aaaa", table "dbo.HotelSummary", column 'HotelSummaryID'.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_History_HotelSummary' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.

Thanks

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 05:57:16

that means there's some data already present in your table which is breaking the referential integrity which foreign key is trying to enforce.
To check it, see what below query returns


SELECT h.HotelSummaryID
FROM [dbo].[History] h
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[HotelSummary] WHERE [HotelSummaryID] = h.[HotelSummaryID])

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources