Great question—those messy auto-generated names like PK__CarRenta__3213E83F2E5BD364 are a nightmare for maintainability, so stopping this at the source is way better than just cleaning up after the fact. Here are the most effective ways to enforce explicit naming for keys and indexes:

1. Use DDL Triggers to Block Unnamed Objects #

DDL triggers let you intercept and stop schema changes that don’t follow your naming rules. Below is a sample trigger that blocks creation of unnamed primary keys, foreign keys, and unique constraints:

CREATE TRIGGER EnforceExplicitConstraintNames
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Command NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');

    -- Block unnamed PRIMARY KEY constraints
    IF @Command LIKE '%PRIMARY KEY%' AND @Command NOT LIKE '%CONSTRAINT %PRIMARY KEY%'
    BEGIN
        RAISERROR('Explicit constraint names are required for PRIMARY KEYs. Please name your constraint before proceeding.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- Block unnamed FOREIGN KEY constraints
    IF @Command LIKE '%FOREIGN KEY%' AND @Command NOT LIKE '%CONSTRAINT %FOREIGN KEY%'
    BEGIN
        RAISERROR('Explicit constraint names are required for FOREIGN KEYs. Please name your constraint before proceeding.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- Block unnamed UNIQUE constraints (optional, adjust as needed)
    IF @Command LIKE '%UNIQUE%' AND @Command NOT LIKE '%CONSTRAINT %UNIQUE%'
    BEGIN
        RAISERROR('Explicit constraint names are required for UNIQUE constraints. Please name your constraint before proceeding.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;
GO

Note: This trigger checks for missing CONSTRAINT clauses in the T-SQL command. You might need to tweak the pattern matching to handle line breaks or non-standard formatting. For more robust parsing, you could use SQL Server’s XML tools, but this basic version works for most standard cases.

2. Policy-Based Management (PBM) for Visual Enforcement #

If you prefer a graphical approach over writing triggers, SQL Server’s Policy-Based Management (PBM) is a great option:

• Create a new policy targeting database or table objects.
• Define a condition that flags constraints where the name starts with PK__, FK__, or IX__ (the default auto-generated prefixes).
• Set the policy to On Change: Prevent to block any attempt to create an unnamed constraint instantly.

PBM integrates seamlessly with SSMS and is easy to adjust as your naming standards evolve.

3. Lock Down Development Workflows #

Technical controls are key, but combining them with process safeguards ensures long-term compliance:

• Mandate explicit names in scripts: Make named constraints/indexes a non-negotiable part of your team’s DDL coding standards.
• Add to code reviews: Include a check for unnamed objects in your code review checklist to catch issues early.
• Use migration tools: Tools like Flyway or Liquibase enforce script-based deployments, making it harder for unnamed objects to slip into production.
• Educate your team: Explain why explicit naming matters—easier debugging, clearer documentation, and avoiding unexpected breakages when modifying schemas.

Bonus: Edge Case Handling #

• Implicit indexes: When you create a named PK or UNIQUE constraint, SQL Server automatically uses that name for the underlying index (you can also specify a separate index name if needed). Enforcing named constraints covers these implicit indexes.
• Third-party apps: If external tools generate DDL without names, work with the vendor to adjust their output, or use a proxy layer to rewrite the DDL before it hits your database.

内容的提问来源于stack exchange，提问作者Martin Guth