Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Beware to the System Generated Constraint Name


Beware to the System Generated Constraint Name

Author
Message
Greg Larsen
Greg Larsen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 290
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
David.Poole
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 3110
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

Newbie on www.simple-talk.com
Greg Larsen
Greg Larsen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 290
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
David.Poole
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 3110
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

Newbie on www.simple-talk.com
Greg Larsen
Greg Larsen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 290
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
David.Poole
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 3110
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

Newbie on www.simple-talk.com
Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 3110
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

Newbie on www.simple-talk.com
Sandeep Saretha
Sandeep Saretha
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 389
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search