Glen_D
asked on
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_ProjectQualificati on 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.
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_ProjectQualificati
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.
ASKER
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_ProjectQualificati on
where QualificationID = @pe)
set @q2 = (select COUNT (QualificationID) from dbo.tbl_ProjectEmployee
where QualificationID = @pe)
set @count = (@q1 - @q2)
If @count <= @p1
then
Else
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_ProjectQualificati
where QualificationID = @pe)
set @q2 = (select COUNT (QualificationID) from dbo.tbl_ProjectEmployee
where QualificationID = @pe)
set @count = (@q1 - @q2)
If @count <= @p1
then
Else
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_ProjectQualificati on
where QualificationID = @qualificationID
)
end
Everything wa sgreat...
Thanks
Glen
Create Function dbo.fn_QualificationCount (@qualificationID int) returns int
as
Begin
return (select QualificationCount
from dbo.tbl_ProjectQualificati
where QualificationID = @qualificationID
)
end
Everything wa sgreat...
Thanks
Glen
glad I could help
sorry for the type, I am working in so many programming languages that sometimes I mess up ...
CHeers
sorry for the type, I am working in so many programming languages that sometimes I mess up ...
CHeers
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