Beware to the System Generated Constraint Name

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Larsen » Beware to the System Generated Constraint...

Beware to the System Generated Constraint Name

Rate Topic

Display Mode

Topic Options

Author

Message

Greg Larsen

Posted Wednesday, August 14, 2002 12:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com

Post #6167

David.Poole

Posted Thursday, August 15, 2002 1:45 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 2,749, Visits: 1,407

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?

LinkedIn Profile

Post #39635

Greg Larsen

Posted Thursday, August 15, 2002 8:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com

Post #39636

David.Poole

Posted Thursday, August 15, 2002 9:26 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 2,749, Visits: 1,407

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.

LinkedIn Profile

Post #39637

Greg Larsen

Posted Thursday, August 15, 2002 9:41 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com

Post #39638

David.Poole

Posted Thursday, August 15, 2002 10:00 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 2,749, Visits: 1,407

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.

LinkedIn Profile

Post #39639

Tatsu

Posted Tuesday, August 27, 2002 2:04 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:43 AM
Points: 287, Visits: 213

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

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog

Post #39640

David.Poole

Posted Monday, September 09, 2002 3:11 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:35 AM
Points: 2,749, Visits: 1,407

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.

LinkedIn Profile

Post #39641

Sandeep Saretha

Posted Friday, May 09, 2008 11:02 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:04 PM
Points: 283, Visits: 356

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

Post #498093

« Prev Topic | Next Topic »

Permissions