THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help

Re: Can I force constraint names?

  •  08-25-2008, 14:14

    Re: Can I force constraint names?

    I do not know of a way to enforce constraint naming conventions using SQL Server 2008 Policy-Based Management. And although you can use DDL triggers, you will have to write code to check for name use as not all constraints require a name when adding to the columns directly in the CREATE TABLE or ALTER TABLE statements.

    For example, you can add primary keys and defaults without a name as follows:

    CREATE TABLE dbo.Table_1
       
    (
       
    id INT NOT NULL PRIMARY KEY
    ,
       
    id2 INT 
    NOT NULL,
       
    id3 INT NOT NULL DEFAULT (1
    )
       )  
    ON 
    [PRIMARY]
    GO 
     

    The next example is a DDL trigger that will check for any constraints being added and print "CONSTRAINT!" whenever a constraint is added to a table (new or altered):

    CREATE TRIGGER trTableCheckConstraint
    ON DATABASE 
    FOR 
    DDL_TABLE_EVENTS
    AS 
       DECLARE 
    @x XML@sql NVARCHAR(MAX
    )
       
    SET @x EVENTDATA
    ()

       
    SET @sql UPPER(@x.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]''nvarchar(max)'
    ))

       
    IF (@sql LIKE '%CONSTRAINT%' OR @sql LIKE '%DEFAULT%' OR @sql LIKE '%PRIMARY%'
    )
       
    BEGIN
           PRINT 
    'CONSTRAINT!'
       
    END 
       
    GO 

    In order to enforce a name, however, you will have to do some string parsing (RegEx would be ideal for this of course, if you are so inclined to add a CLR-based Scalar User-Defined Function), as the CommandText will contain the actual SQL statement that was executed. You will need to check in the CommandText for all types of constraints, preferably in a UDF, and rollback if no name is used, or if the name does not comply with your naming standards. And the initial check may return false positives (such as a column with the word 'Primary' in its name), so be sure to do due diligence when validating the constraints.

    Filed under: , ,
View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement