Beware to the System Generated Constraint Name

  • Greg Larsen

    SSC-Insane

    Points: 20605

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/glarsen/sysgenconstraintnames.asp

    Gregory A. Larsen, MVP

  • David.Poole

    SSC Guru

    Points: 75083

    I started off with SQL 6.5 when Enterprise Manager was given the euphenism Enterprise Mangler therefore we always used TRANSACT SQL and explicitly named every constraint.

    SQL also creates system indices when it needs that cause the same problems as discussed in this article.

    In SQL 6.5 if you didn't delete the indices BEFORE you DROPped a table then you would end up with an orphaned index, therefore I set up a routine to remove all constraints and indices prior to dropping the table.

    In SQL 7/2000 my routine keeps hitting system indices and falling over.

    Does anyone know a way of identifying system indices or constraints?

  • Greg Larsen

    SSC-Insane

    Points: 20605

    Try this:

    -- finding system generated constraint names

    select name from sysobjects

    where

    substring(name,rtrim(len(name))-8,len(name)) like '[__][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F]'

    -- finding system genereated indexes

    select name from sysindexes

    where

    name like '[_]WA%'

    quote:


    I started off with SQL 6.5 when Enterprise Manager was given the euphenism Enterprise Mangler therefore we always used TRANSACT SQL and explicitly named every constraint.

    SQL also creates system indices when it needs that cause the same problems as discussed in this article.

    In SQL 6.5 if you didn't delete the indices BEFORE you DROPped a table then you would end up with an orphaned index, therefore I set up a routine to remove all constraints and indices prior to dropping the table.

    In SQL 7/2000 my routine keeps hitting system indices and falling over.

    Does anyone know a way of identifying system indices or constraints?


    Gregory A. Larsen, MVP

  • David.Poole

    SSC Guru

    Points: 75083

    I thought this at first, however it is not as simple as the naming convention.

    Not all system indices start __WA etc.

    The way I get around it at present is to use my own naming convention and anything that doesn't conform to my convention must be a system object.

    This is fine when I have absolute control over my databases, however I need something more robust because my customers won't necessarily have the same methodology that I use.

  • Greg Larsen

    SSC-Insane

    Points: 20605

    What system generated indexes don't start with "_WA" (although these are not really indexes, only statistics)? If you are referring to system generated primary keys then try this on sysindexes:

    select name from sysindexes

    where

    substring(name,rtrim(len(name))-8,len(name)) like '[__][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F]'

    Of course this is not robost, unless all system generated names always end with a '_' followed by a 8 character hex number. Plus someone could also name there non-system generated name with this same convention.

    If you are not talking about system generated primary keys then, what other indexes might you be referring too?

    Gregory A. Larsen, MVP

  • David.Poole

    SSC Guru

    Points: 75083

    I had a table called Tbl_CommenceDownload and noticed that I gained an index called tTbl_CommenceDownload.

    I attempted to drop this and got the standard message that tells you that you are trying to drop a system object.

  • Tatsu

    SSCertifiable

    Points: 7824

    Another reason to name your constraints is to help debug applications. A foreign key violation message states which column(s) in the key table were violated but doesn't tell you the referencing table or column names. My standard for naming foreign keys is as follows:

    FK_<RefTableName>$<RefColumn1>$<RefColumnN>

    Since the error message does show the contraint name, I get all the tables and columns involved in the error without having to step through every line of code in my script or application.

    BTW, I use $ instead of underscores because some people (myself excluded) use underscores in their object names.

    Bryant E. Byrd, MCDBA

    SQL Server DBA/Systems Engineer

    Intellithought, Inc.

    bbyrd@intellithought.com

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • David.Poole

    SSC Guru

    Points: 75083

    I DO name my constraints.

    If SQL needs an index and there isn't one available it creates its own.

    Sometimes an index is not appropriate but SQL may decide that it needs one.

    In the particular example I have given my table is a flat intermediate file used for preprocessing data from an external source before appending it into my applicaton database.

  • Sandeep Saretha

    SSChasing Mays

    Points: 647

    I am 100% agree with this article. System generated name cause so much problem when you are comparing two databases. Lets say If you want to compare Development and production database with system generated constraints name , PK Name etc. It will throw bunch of differences. It is good practice to have manually defined name.

    Thanks

    Sandeep

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply