• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1111
  • Last Modified:

check constraint

I have two tables:

tbl_ProjectEmployee
PK-QualficiationID (Part of composite PK)

tbl_ProjectQualification
PK-QualificationID (Part of Composite PK)
Qualification Count

I want to place a check constraint on tbl_ProjectEmployee that does not exceed the QualificationCount in the second table.

Here's my code but I'm stuck:

ALTER TABLE dbo.tbl_ProjectEmployee ADD CONSTRAINT CK_QualificationID
    CHECK (QualificationID <=  (Select QualificationCount from dbo.tbl_ProjectQualification pq
    where pk.QualificationID = QualificationID)

Error:

Msg 1046, Level 15, State 1, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.


0
Glen_D
Asked:
Glen_D
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as the error indicates: you cannot do that with a subquery.

however, as from sql 2008, you can workaround by creating a function:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/02/07/using-a-udf-in-a-check-constraint-to-check-validity-of-history-windows-start-end-date-windows.aspx
0
 
Glen_DAuthor Commented:
I thought a function may work better but stuck with this as well:

Create Function (fn_QualificationCount) (@qualificationID int)
as
Begin
Declare @pe int
Declare @q1 int
Declare @q2 int
Declare @count int
set @pe = (select QualificationID from dbo.tbl_ProjectEmployee)
set @q1 = (select QualificationCount from dbo.tbl_ProjectQualification
                  where QualificationID = @pe)
set @q2 = (select COUNT (QualificationID) from dbo.tbl_ProjectEmployee
                  where QualificationID = @pe)
set @count = (@q1 - @q2)

If @count <= @p1
then

Else

            
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's see:
Create Function dbo.fn_QualificationCount (@qualificationID int) as int
as
Begin
 return (select QualificationCount 
              from dbo.tbl_ProjectQualification
             where QualificationID = @qualificationID
            )
end

Open in new window


and your constraint goes like this:
ALTER TABLE dbo.tbl_ProjectEmployee ADD CONSTRAINT CK_QualificationID
    CHECK (QualificationID <= dbo.fn_QualificationCount (QualificationID))

Open in new window

0
 
Glen_DAuthor Commented:
Angel...that was great except for a minor syntax error in the function:

Create Function dbo.fn_QualificationCount (@qualificationID int) returns int
as
Begin
 return (select QualificationCount
              from dbo.tbl_ProjectQualification
             where QualificationID = @qualificationID
            )
end

Everything wa sgreat...

Thanks

Glen
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help

sorry for the type, I am working in so many programming languages that sometimes I mess up ...

CHeers
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now