How to name table-variable function unique constraint?

Question

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as follows:

CREATE FUNCTION [dbo].[fn_name] (...)
RETURNS @Result 
TABLE
(
    ID BIGINT PRIMARY KEY,
    ...
    RowNum BIGINT UNIQUE 
)
BEGIN
    ...
    RETURN
END
GO

I have try to name it like this, but is not working:

CREATE FUNCTION [dbo].[fn_name] (...)
RETURNS @Result 
TABLE
(
    ID BIGINT PRIMARY KEY,
    ...
    RowNum BIGINT
    ,CONSTRAINT UC_fn_name_RowNum UNIQUE([RowNum])  
)
BEGIN
    ...
    RETURN
END
GO

Is it possible to set the name of a unique constraint when it is part of table variable function definition?

Answer 1

Table Variables, whether created from a DECLARE or from a RETURNS in a CREATE FUNCTION statement, as well as User-Defined Table Types (UDTTs), do not allow for constraints to be named. According to the MSDN page for CREATE FUNCTION:

Arguments
...
        < column_constraint >::= and < table_constraint>::=
        ... Named constraints are not allowed.

A "stated" exception to this rule is found on the MSDN page for DECLARE @local_variable which states (under the explanation of DEFAULT):

To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

However, it appears that this is an error with the documentation as I am unable to assign a name to a DEFAULT Constraint, there is no description of the syntax to accomplish this, and there are no examples of doing this in the documentation (I looked as far back as SQL Server 2000).

When it comes to Temporary Tables the issue is a bit more straight forward: if you explicitly name any constraints, that would disallow the code from running in two different sessions at the same time. Constraints are objects (unlike Indexes) and need to have unique names within the same schema. Hence, if some code creates a local Temporary Table that has constraints that are given names, then if two sessions run that code at the same time, the second session would get an error when it went to create its Temporary Table due to the name conflict (which occurs in tempdb, where Temporary Tables exist). For example, run the following in one query tab in SSMS:

CREATE TABLE #Session1 (Col1 INT NOT NULL CONSTRAINT [UQ_DifferentSession] UNIQUE);

And in another query tab, run the following:

CREATE TABLE #Session2 (Col1 INT NOT NULL CONSTRAINT [UQ_DifferentSession] UNIQUE);

Even with the Temporary Tables having different names in addition to being local Temporary Tables created in different sessions, you will still receive the following error:

Msg 2714, Level 16, State 5, Line 1
There is already an object named 'UQ_DifferentSession' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Similar to Temporary Tables, Table Variables also do not have any definition until they are "declared" and then that definition only resides in tempdb. But we can't test for name collisions on Table Variables since you cannot name their constraints.

Returned tables in Multi-statement TVFs, and User-Defined Table Types, on the other hand, exist a little differently than Temporary Tables in that they store their definition in the database where they exist. But the meta-data they store in their local database is never actually used to store data; their meta-data is only used as a template for what will get created (when needed) in tempdb. And when that meta-data "template" is used, the named objects in tempdb (Primary Keys, Unique Constraints, Default Constraints, and Check Constraints) get dynamically generated names in the form of UQ__#BC4FB52__A259EE56FAB8BB6F.

---

Regarding the desire to name the constraints in order to be in line with naming conventions (as stated in a comment on the Question):

While it wouldn't hurt anything to use sp_rename (as pointed out in @Peter's answer), it would just be a superficial change. One of the main reasons to have a naming convention is for maintenance, so that if you need to ALTER or DROP the object later, you won't need to write queries to discover what is there and then concatenate that into Dynamic SQL. But that concern isn't relevant here since there is no way to ALTER or DROP them anyway.

Also, any name change done via sp_rename would be short-lived since any ALTER FUNCTION will drop and recreate the Constraints, and hence give them newly generated object names. This might could be handled programmatically with a DDL Trigger that captures CREATE FUNCTION, ALTER FUNCTION, and CREATE TYPE statements, but the end-result of that effort won't be that your life is any easier ;-). It would just be that selecting from sys.objects, sys.indexes, and sys.key_constraints looks "cleaner".