Can default object names be changed?

Question

Sooner or later, a db developer will come across, and possibly use, a naming scheme for many objects like foreign keyes, default constraints etc.

Let's consider this example: Name all foreign keys as:
FK__[name of source table]__[name of source col]__ref__[name of master table]__[name of master column]

What kind of parametrization/external programs could be used to achieve having such a name as default, when for example using a create table statements which mentions the fk but does not explicitly provide a constraint name?

Answer 1

What kind of parametrization/external programs could be used to achieve having such a name as default, when for example using a create table statements which mentions the fk but does not explicitly provide a constraint name?

There's no way to change the default name generated when the DDL doesn't contain a name. The only thing you can do is to run a batch process to identify non-compliant objects and perhaps drop and recreate them.

Answer 2

If you want to discourage creation of auto-named constraints, you can do it using database-level DDL trigger for CREATE_TABLE and ALTER_TABLE events.

Such as, for example^(*)

CREATE TRIGGER [ConstraintNamingCheck] ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @event xml, @e_type varchar(30), @s_name sysname, @o_name sysname, @o_id int, @is_filetable bit;
    SET @event = EVENTDATA();
    SET @e_type = @event.value('(/EVENT_INSTANCE/EventType/text())[1]', 'varchar(30)');
    SET @s_name = @event.value('(/EVENT_INSTANCE/SchemaName/text())[1]', 'sysname');
    SET @o_name = @event.value('(/EVENT_INSTANCE/ObjectName/text())[1]', 'sysname');
    SELECT @o_id = t.object_id, @is_filetable = t.is_filetable
    FROM sys.tables t
        JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE s.name = @s_name AND t.name = @o_name;

    IF @is_filetable = 1
        RETURN;

    DECLARE @msg_constraints nvarchar(max), @msg nvarchar(max);
    SELECT @msg_constraints = STRING_AGG(QUOTENAME(c.name), CHAR(13) + CHAR(10))
    FROM (
        SELECT name FROM sys.key_constraints WHERE parent_object_id = @o_id AND is_system_named = 1
        UNION ALL
        SELECT name FROM sys.foreign_keys WHERE parent_object_id = @o_id AND is_system_named = 1
        UNION ALL
        SELECT name FROM sys.check_constraints WHERE parent_object_id = @o_id AND is_system_named = 1
        UNION ALL
        SELECT name FROM sys.default_constraints WHERE parent_object_id = @o_id AND is_system_named = 1
    ) c(name)
    WHERE @e_type = 'CREATE_TABLE'
        OR @e_type = 'ALTER_TABLE'
        AND @event.exist('/EVENT_INSTANCE/AlterTableActionList/Create/Constraints/Name[text()=sql:column("c.name")]') = 1;

    IF @msg_constraints IS NULL OR @msg_constraints = ''
        RETURN;

    SET @msg = 'Table ' + QUOTENAME(@s_name) + '.' + QUOTENAME(@o_name) + ' is being '
        + LOWER(LEFT(@e_type, 5)) + 'ed with one or more auto named constraints:'
        + CHAR(13) + CHAR(10) + @msg_constraints
        + CHAR(13) + CHAR(10) + 'Please specify constraint names explicitly.';

    THROW 51011, @msg, 1;
    ROLLBACK TRANSACTION;
END
GO

Once you have it in database, things like

CREATE TABLE TableName
(
    id int NOT NULL,
    parent_id int NULL,
    name varchar(20) NOT NULL,
    dateCreated datetime NOT NULL DEFAULT (GETDATE()),
    value1 float NOT NULL,
    value2 tinyint NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (parent_id) REFERENCES TableName,
    UNIQUE (name),
    CHECK ((value1 BETWEEN 0 AND 1) AND (value2 IN (1, 2, 4, 8)))
);

(and equivalent ALTER) will not be allowed.

The error message thrown

Msg 51011, Level 16, State 1, Procedure ConstraintNamingCheck, Line 45 [Batch Start Line 50]
Table [dbo].[TableName] is being created with one or more auto named constraints:
[PK__TableNam__3213E83FB1491772]
[UQ__TableNam__72E12F1BE94335EB]
[FK__TableName__paren__316D4A39]
[CK__TableName__32616E72]
[DF__TableName__dateC__30792600]
Please specify constraint names explicitly.

will remind you or your colleague to name constraints explicitly. It can be modified to suggest the "right" name additionally.

Before you add the trigger, existing constraint names can be normalized using sp_rename, for example

EXEC sp_rename 'dbo.PK__TableNam__3213E83FB1491772', 'PK_TableName', 'OBJECT';

You will have to grab info for renaming parameters from system DMVs (those same which are used in DDL trigger) for constraints having is_system_named = 1 and then do renaming in a loop or cursor.

---

Those who want to have database option for disallowing system named constraints in future releases of SQL Server, such as

ALTER DATABASE [DbName]
    SET CONSTRAINT_NAMING = { DEFAULT | EXPLICIT };

can cast a vote for my initiative here.

---

^(*) There are some known issues regarding this DDL trigger:

For SQL Server versions before 2017 Another string concatenation technique should be used instead of STRING_AGG (based on FOR XML approach for example).

For SQL Server 2014 and later versions Trying to create table in memory optimized filegroup raises Msg 12332 error

Msg 12332, Level 16, State 111, Line 87
Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

The workaround is to DISABLE TRIGGER before creating memory optimized table and ENABLE TRIGGER after (obviously naming checks will not happen during this period).

For SQL Server 2005-2008R2 RAISERROR should be used instead of THROW and is_filetable check should be removed.