469,914 Members | 2,262 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,914 developers. It's quick & easy.

Altering functions and CHECK constraints

Let's say I create a multi-statement function like this:

CREATE FUNCTION dbo.Test ()
RETURNS @res TABLE (N int NOT NULL CHECK (N >= 0))
AS
BEGIN

INSERT INTO @res
SELECT 1

RETURN
END

That works fine. Then I make a change in the function's body, replace the
CREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:

Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9
Cannot ALTER 'dbo.Test' because it is being referenced by object
'CK__Test__N__5D2E32EB'.

Indeed, if I look at the list of dependencies for the function in QA's
object tree, I can see the check constraint referenced in the error
message.

ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
definition of the @res table.

So it seems that the only way to modify such a function is to drop and
recreate. Is that a known behavior? Is there any particular reason for it?

Thanks.

--
(remove a 9 to reply by email)
Jul 23 '05 #1
1 4316
Dimitri Furman (df*****@cloud99.net) writes:
ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
definition of the @res table.

So it seems that the only way to modify such a function is to drop and
recreate. Is that a known behavior? Is there any particular reason for it?


I will have to admit that I was not aware of this. As for why, my guess
is that this is an artefact of the metadata structure in SQL Server, and
the SQL Server developers did not write the necessary code to avoid this.

Anyway, the restriction is not there in SQL 2005, so whatever the reason
for this in SQL 2000, it is not likely to be a compelling one.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by vegetax | last post: by
9 posts views Thread by Edmund Dengler | last post: by
5 posts views Thread by Jurgen Defurne | last post: by
9 posts views Thread by JimmyKoolPantz | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.