functions in check constraint

Hi there,
Is it possible to modify a function used in a check constraint, without
having to drop the constraint first?
E.G.
Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
@.StatusId int) Returns bit As
Begin
declare @.RetVal bit
if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
Set @.RetVal = 1
else
Set @.RetVal = 0
Return @.RetVal
End
go
Alter table dbo.SampleItemIssue Add Constraint
CK_SampleItemIssue_StatusTypeId Check(
dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
)
go
Alter function dbo.CheckSampleItemIssueStatus(...
returns an error along the lines of cannot alter function because it is
referenced by constraint..
Thanks.
Fred.
You have to drop the constraint first, before you can change the function.
What does the function GetSampleItemIssueStatus do? Because I think you can
solve this with foreign keys or otherwise without having to use functions.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
> Hi there,
> Is it possible to modify a function used in a check constraint, without
> having to drop the constraint first?
> E.G.
> Create function dbo.CheckSampleItemIssueStatus (@.sampleItemIssueId int,
> @.StatusId int) Returns bit As
> Begin
> declare @.RetVal bit
> if(@.StatusId = dbo.GetSampleItemIssueStatus(@.sampleItemIssueId))
> Set @.RetVal = 1
> else
> Set @.RetVal = 0
> Return @.RetVal
> End
> go
> Alter table dbo.SampleItemIssue Add Constraint
> CK_SampleItemIssue_StatusTypeId Check(
> dbo.CheckSampleItemIssueStatus(SampleItemIssueId, StatusTypeId) = 1
> )
> go
> Alter function dbo.CheckSampleItemIssueStatus(...
> returns an error along the lines of cannot alter function because it is
> referenced by constraint..
>
> Thanks.
> Fred.
>
|||Thanks for the reply,
You confirmed my thoughts, I guess what I'm after is something like
Alter table disable/enable trigger, but for constraints.
That function is just an example and i can't do it via foreign keys,
because the rules governing the value of the statusId are based in part on
records from other tables.
In an other case I also need to check that a number matches the luhn
algorithm.
(http://www.brainyencyclopedia.com/en...algorithm.html)
Cheers.
"Jacco Schalkwijk" wrote:

> You have to drop the constraint first, before you can change the function.
> What does the function GetSampleItemIssueStatus do? Because I think you can
> solve this with foreign keys or otherwise without having to use functions.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:5EB25407-CCB1-4D31-A6A8-0AA62DB6D19D@.microsoft.com...
>
>