Drop default constraint on a table function

Question

I need to drop some default constraints that appear to be tied to table functions (and not actual tables).  This means when I try the ALTER TABLE DROP CONSTRAINT command it fails with the error, "unable to drop constraint because object is not a table" or something similar.

My question is: how do I drop a constraint on a table function?

Answer 1

What is a "table function"?  Did you mean a table-valued function? If so, the table is defined by the function and you cannot alter it from "outside" since the table only exists for the duration of the statement that executes the function.  

Answer 2

You are correct.  I mean a table-valued function.  Some of these functions have default constraints on them, and I'm not certain how they even got there, nor do I know how to get rid of them.  Any help is much appreciated.  Thanks!

Answer 3

As I said, the table generated by a TVF has a very short lifespan and you cannot alter the schema of the table after executing the function.  So what exactly are you trying to do?  The fact that such a table has a default constraint should have no bearing on using the table in a query.   OTOH, if you need to alter the schema of the table generated by the TVF you must alter the definition of the function.  But again, it is not clear why one would need to do such a thing.

Answer 4

This issue was encountered while following our process for handling replication on an OLTP database used by our proprietary application that had to be upgraded.  One of the steps after recreating the subscriber db (we initialize the from a backup file, fyi, so recreating the subscriber is literally creating a full backup of the publisher and restoring as the subscriber db on the subscriber server, and then create the subscription with the @sync_type
= 'initialize with backup' option) is to drop all constraints on the subscriber database.  We use a proc that creates a list of all constraints by joining the sysobjects and sysforeignkeys tables and then executes an ALTER TABLE DROP CONSTRAINT command for each one found.  This script would capture these TVF constraints but when ALTER TABLE was executed they would fail because they aren't real tables... this is what prompted my initial question.

We are in the process of rewriting this process so it will exclude these TVF constraints, but I wasn't sure if I needed to be concerned that they were there.  I'm afraid I don't have much knowledge of TVFs.

Answer 5

you can use condition like

WHERE sysobjects.type <> 'TF'

to filter out constraints on TVFs

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 6

Agreed.  In fact, that's the route we've taken to workaround this.

But can you tell me how those default constraints get on those TVFs or why they're put there (if done by SQL?) Also, you mentioned the "tables generated by a TVF has a very short lifespan."  Do you mean that these TVF constraints also have a short life span?  I guess I'm just trying to understanding the process and reasons these TVF constraints were there and how they're used.  If there's any literature you can suggest that would adequately explain I'd like to take a look at it.  Thanks again for your help.

Answer 7

I think someone created constraints inside TVF as per standards they followed. I've never seen instances like that myself, we sometimes create default but doesnt create them as constraints inside TVFs.

tables generated by a TVF has a very short lifespan  means the table has scope only within the function. As soon as function is executed and result returned table is out of scope. So there's no need to worry about the table, columns and their constraints after.

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 8

I suggest you review the documentation for TVFs and how they are (and can be) used.  The table returned by a TVF (and in this case I refer specifically to multistatement TVFs) are defined using a subset of the create table syntax.  They can be created with constraints of different types - not just defaults.  Why?  Because it suits the logic of the developer and (perhaps) because it assists the database engine or the logic that depends on the output of the function.  

Below is one example that I used (written by Steve Kass) from a LONG time ago.  Notice the primary key.

CREATE FUNCTION [dbo].[uf_sequence] (@N int) 
RETURNS @T TABLE ( 
   seq int not null primary key clustered
  ) 
AS 
/*
**  04/21/05.sbm - Bug #306.  Initial version.  
**                 Code provided by Steve Kass - MS .programming newsgroup
*/
BEGIN 

DECLARE @place int 
SET @place = 1 

INSERT INTO @T (seq) VALUES (0) 

WHILE @place <= @N/2 BEGIN 
   INSERT INTO @T (seq) 
   SELECT @place + Seq FROM @T 

   SET @place = @place + @place 
END 

INSERT INTO @T (seq) 
SELECT @place + Seq FROM @T 
WHERE Seq <= @N - @place 

RETURN 
END 
go

For your particular case, the choice of a default constraint is likely due to the implementation of the logic in the function.  Perhaps there are multiple insert statements and it was simpler/easier/more robust to use a default constraint rather than repeatedly hard-code the value in each statement.  By choosing a default constraint, the developer need only alter the constraint (once) if the value needs to be changed rather than finding and changing each statement that inserts or updates the table.   

As you've have already discerned, you can simply ignore any constraints that are defined on the tables returned by a TVF.