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:
< 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
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
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
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
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.key_constraints looks "cleaner".