Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Beware to the System Generated Constraint Name Expand / Collapse
Author
Message
Posted Wednesday, August 14, 2002 12:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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
Posted Thursday, August 15, 2002 1:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 2,909, Visits: 1,837
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
Post #39635
Posted Thursday, August 15, 2002 8:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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
Posted Thursday, August 15, 2002 9:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 2,909, Visits: 1,837
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
Post #39637
Posted Thursday, August 15, 2002 9:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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
Posted Thursday, August 15, 2002 10:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 2,909, Visits: 1,837
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
Post #39639
Posted Tuesday, August 27, 2002 2:04 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:31 PM
Points: 295, Visits: 282
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
Posted Monday, September 9, 2002 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:31 AM
Points: 2,909, Visits: 1,837
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
Post #39641
Posted Friday, May 9, 2008 11:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:59 PM
Points: 287, Visits: 386
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 »

Add to briefcase

Permissions Expand / Collapse