Time-out error when adding a check constraint to a table

Question

Post reply

Time-out error when adding a check constraint to a table

ed_ward_graham

Mr or Mrs. 500

Points: 587
More actions
June 20, 2011 at 9:44 am

#239457

Hi all,

I am trying to add a check constraint to one table that refers to columns in two others. Following a suggestion from StackOverflow I have created a user-defined function to perform my checking:

CREATE FUNCTION [dbo].[ufn_tblSuperMapTblMapCheckConstraint]() RETURNS INT AS BEGIN RETURN ( select COUNT(*) from tblSuperMap tSM left join ( select distinct tM.MapID, tS.SeriesID from tblMap tM inner join tblSurvey tS on tM.SurveyID = tS.SurveyID ) t1 on tSM.MapID = t1.MapID and tSM.SeriesID = t1.SeriesID where t1.MapID is null ) END

I refer to this function in the constraint as follows:

ALTER TABLE tblSuperMap ADD CONSTRAINT CK_tblSuperMap_tblMap CHECK (dbo.ufn_tblSuperMapTblMapCheckConstraint() = 0) GO

When I try to execute the ALTER TABLE statement, the execution never finishes! (I waited for 20 minutes before cancelling the query.) If I try it from the table's Design View in SSMS, I eventually get a time-out message.

Is my function ill-formed in some way? If I change it to

CREATE FUNCTION [dbo].[ufn_tblSuperMapTblMapCheckConstraint]() RETURNS INT AS BEGIN RETURN (0) END

then everything works (but is not useful); but if I try even something like

CREATE FUNCTION [dbo].[ufn_tblSuperMapTblMapCheckConstraint]() RETURNS INT AS BEGIN RETURN ( select COUNT(*) from tblSuperMap ) END

then the hang/time-out issue appears again.

Any thoughts gratefully appreciated: I haven't found anything on Google and have restarted the SQL service several times. I am using SQL 2008 R2 on a 64-bit W2K8 platform (I have tried W2K8 and R2).

Ed Graham

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm going to guess that one or more of the tables you are checking against have a significant number of rows in them.

What's the actual purpose of the constraint? It appears to be checking that no rows are added that would not match some other tables, and foreign key constraints are usually better for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

spaghettidba SSC Guru Points: 105712 More actions · Answer 2

spaghettidba

SSC Guru

Points: 105712

June 20, 2011 at 10:01 am

#1341296

Unless you add the constraint unchecked, SQLServer tries to check the constraint against all the existing rows.

Scalar functions usually don't perform well.

I'll second GSquared suggestion: use FK constraints for this kind of check.

-- Gianluca Sartori

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

I second GSquared and Gianluca suggestion too.

If your constraint cannot refer to a primary key or another unique constraint, replace the counting to existance check because your check = 0 (or > 0 ) ! (and modify it to a itvf in stead a svf if you can ! )

Keep in mind to support the things you need with indexes !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ed_ward_graham Mr or Mrs. 500 Points: 587 More actions · Answer 4

Thanks for the quick replies! The constraint check refers to two other tables: is there a way of constructing a foreign key that refers to two tables rather than just one? (SSMS doesn't appear to offer this as an option.) I tried making a single view that had all the information I needed but apparently you can't create foreign keys to views. 🙁

You are right: the tables involved are quite large (~50,000 rows and ~10,000 rows). I also agree that a foreign key would be best -- is it possible here?

All advice appreciated.

ed_ward_graham Mr or Mrs. 500 Points: 587 More actions · Answer 5

It seems that the devil was in the detail ... having read the above comments more closely, I have now managed to set my constraint with this syntax (for the benefit of others having the same trouble):



ALTER TABLE tblSuperMap [highlight=#ffff11]WITH NOCHECK[/highlight]

ADD CONSTRAINT CK_tblSuperMap_tblMap

    CHECK (dbo.ufn_tblSuperMapTblMapCheckConstraint() = 0)

GO

Thanks to all for their replies.

GSquared SSC Guru Points: 260824 More actions · Answer 6

You can't directly have a foreign key reference two tables. On the other hand, you shouldn't need to.

If tblMap has a foreign key to tblSurvey, on the survey ID, then referencing each separately should handle this in most cases.

I'd have to see the actual table structures to be sure, but in most cases it should work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ed_ward_graham Mr or Mrs. 500 Points: 587 More actions · Answer 7

Thanks for the reply, GSquared. The database schema is as follows. tblMap has a FK to tblSurvey (SurveyID), as you said; however, its primary key is actually two columns, MapID and SurveyID. tblSurvey has SurveyID as its PK and another column, SeriesID (an FK to tblSeries), representing the fact that each survey belongs to a particular series. My constraint needs to ascertain whether there are any entries in tblMap such that the MapID and SeriesID match a certain pair of values, the SeriesID being inferred from the SurveyID. I don't know how you achieve this with foreign keys but if you have any suggestions I'd be keen to hear them.

Thanks again.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 8

A child table should only refer to the full PK of its parent table. (or another AK is allowed in sqlserver)

as you state you PK of tblSurvey is composed of two columns.

If you do not implement surrogate keys, then you should implement both columns for all child tables to be used as foreign key columns (and indexed to match the PK def.)

edited: Be sure to add "checked" constraints !

ref: http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/