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/encyclopedia/l/lu/luhn_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...
> > 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.
> >
>
>

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...
>
>

function in a constraint

I want to make sure that only month end dates make it into the table. I coul
d
put if conditions in my insert/update stored procs or I could do that with a
constraint.
Is it possible to put a constraint in table that checks for certain
condition using user defined functions?
for example:
CREATE TABLE t1
(
c1 int,
mydate datetime
CONSTRAINT myconstraint month_end_check_function(mydate)
)
Where month_end_check_function is a function that returns true if c2 was
month end date such as 7/31/05 and would return false if c2 was 7/3/05.
TIA...sqlster,
SQL Server does support functions within a check constraint.
HTH
Jerry
"sqlster" <nospam@.nospam.com> wrote in message
news:48C643BE-FA2E-44C3-88DE-2C6596CDEAD1@.microsoft.com...
>I want to make sure that only month end dates make it into the table. I
>could
> put if conditions in my insert/update stored procs or I could do that with
> a
> constraint.
> Is it possible to put a constraint in table that checks for certain
> condition using user defined functions?
> for example:
> CREATE TABLE t1
> (
> c1 int,
> mydate datetime
> CONSTRAINT myconstraint month_end_check_function(mydate)
> )
> Where month_end_check_function is a function that returns true if c2 was
> month end date such as 7/31/05 and would return false if c2 was 7/3/05.
> TIA...
>|||On Fri, 7 Oct 2005 08:45:01 -0700, sqlster wrote:

>I want to make sure that only month end dates make it into the table. I cou
ld
>put if conditions in my insert/update stored procs or I could do that with
a
>constraint.
>Is it possible to put a constraint in table that checks for certain
>condition using user defined functions?
>for example:
>CREATE TABLE t1
>(
>c1 int,
>mydate datetime
> CONSTRAINT myconstraint month_end_check_function(mydate)
> )
>Where month_end_check_function is a function that returns true if c2 was
>month end date such as 7/31/05 and would return false if c2 was 7/3/05.
>TIA...
>
Hi sqlster,
Though Jerry is right - functions are permitted in a CHECK constraint -,
you don't need one here:
CREATE TABLE T1
(c1 int NOT NULL PRIMARY KEY,
mydate datetime,
CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Nice job Hugo - thinkin outside the box!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:8vvdk159t9sefnbp02hn1rgsevlhqhdtu8@.
4ax.com...
> On Fri, 7 Oct 2005 08:45:01 -0700, sqlster wrote:
>
> Hi sqlster,
> Though Jerry is right - functions are permitted in a CHECK constraint -,
> you don't need one here:
> CREATE TABLE T1
> (c1 int NOT NULL PRIMARY KEY,
> mydate datetime,
> CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Excellent (and creative) solution!
Gert-Jan
Hugo Kornelis wrote:
> Hi sqlster,
> Though Jerry is right - functions are permitted in a CHECK constraint -,
> you don't need one here:
> CREATE TABLE T1
> (c1 int NOT NULL PRIMARY KEY,
> mydate datetime,
> CONSTRAINT (MONTH(mydate) <> MONTH(DATEADD(day, 1, mydate)))
> )
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)