Failed to enable constraints. One or more rows con

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)

Failed to enable constraints. One or more rows con

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2013-10-09 : 04:39:18

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

I get the above error message when I run the following code:

SELECT cheq_id, cheq_daterec, cheq_pername, CAST(cheq_amt As decimal(9,2)) As cheq_amt, cheq_behalf, cheq_behalf1, cheq_behalf2, N.NatBusName, D.Dept_tdp, cheq_recpno, cheq_recpno1, cheq_recpno2
FROM RecCheqs As C
JOIN NatureBusiness AS N
ON N.NatBusID = C.cheq_natbus_id
JOIN laintranet1.dbo.tblDepartment_tdpOLD AS D 
ON C.cheq_dept = D.DeptId_tdp
WHERE cheq_DateRec >=  coalesce(nullif(@DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@DateTo, ' '),cheq_DateRec)
AND cheq_dept = coalesce(nullif(@Section , ' '),cheq_dept)
AND cheq_office = @Sdid
ORDER BY cheq_id

@Section could contain the value of 65, 67, or 69 between any of the dates.
@Sdid could contain a string such as 'Tester'

I get the Error message when the @Section value is a number of values between the 2 dates, but if the @Section values are all the same i.e. all 65 or all 67 then there is no Error, it seems to just be an error when there is more than 1 value for @Section.

I have just started getting this error message and I don't know why?
Any ideas ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 05:43:53

I dont think error posted is generated from this statement. The error seems like you're trying to create constraint on a table and its breaking due to availability of conflicting data in the table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

macca
Posting Yak Master

146 Posts

Posted - 2013-10-09 : 06:13:31

Thanks for the reply.
I am running this to feed a datsource .xsd file .. maybe its in this that the error lies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 07:02:50

no idea..The error has no relation to code part you posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

macca
Posting Yak Master

146 Posts

Posted - 2013-10-09 : 07:19:52

The only thing I can think of is that the Error started to appear after I recreated the table laintranet1.dbo.tblDepartment_tdpOLD which is in the code as
JOIN laintranet1.dbo.tblDepartment_tdpOLD AS D

I had to put Deptid_tdp as the Primary Key on this table.

Maybe this has something to do with it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 07:53:41

quote:
Originally posted by macca

The only thing I can think of is that the Error started to appear after I recreated the table laintranet1.dbo.tblDepartment_tdpOLD which is in the code as
JOIN laintranet1.dbo.tblDepartment_tdpOLD AS D

I had to put Deptid_tdp as the Primary Key on this table.

Maybe this has something to do with it?

yep...are you sure Deptid_tdp has unique values and does not contain any NULLs?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

macca
Posting Yak Master

146 Posts

Posted - 2013-10-09 : 09:14:01

Thanks for the help Vishak.
It turned out the problem was with the report. One of the field sizes had changed in the new table and this didnt correspond to the field size in the report so when I set the field size in the report to the same as that of the DB then the Error disappeared.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources