April 16, 2012 at 2:04 pm
I apologize if this is a Captain Oblivious question but Google results is polluted with hits on how to fix up the system generated names but doesn't answer the question of if there's any ramifications to consider when a constraint names. I'm fairly sure that unlike columns, renaming a constraint won't break other objects but I've been wrong before, so decided I'd at least ask the obvious question just to ease the nagging little voice in my head.
So, is there any potential negative ramification if one were to rename a constraint?
Addendum: I guess the main reason behind the nagging little voice boils down to this, "if constraint name was meaningless, why would they insist on requiring a name for constraint?". I think life would be easier if it wasn't required as a part of ALTER TABLE but life is a bowl of cherries.
April 16, 2012 at 2:08 pm
Shouldn't hurt anything unless you have something querying a specific constraint name for some reason. They have names so you can add and remove them easily. I don't like to use system generated names because they have no meaning. How would you identify a constraint without a name? I don't understand your logic in that last part of your response at all...
Jared
CE - Microsoft
April 16, 2012 at 2:10 pm
Little to none. The only risk is for primary key or unique constraints where you may have an index hint referencing the constraint by name. Other than that, change at will.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 2:16 pm
Thanks Jared & Gail for reassurance. I'm fairly sure I do not reference constraints in anything permanent.
Jared - In MySQL, to drop a default constraint, the syntax is thus:
ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;
No need to name a default constraint, which probably account for bulk of constraints in SQL Server. You're right that one still have to name foreign keys & unique constraints, though. I guess it's me being a bit annoyed with being required to reference constraints explicitly as part of change scripting in the ALTER TABLEs and not appreciating the need to name constraints until just now.
Again, thanks!
April 16, 2012 at 2:25 pm
Banana-823045 (4/16/2012)
Thanks Jared & Gail for reassurance. I'm fairly sure I do not reference constraints in anything permanent.Jared - In MySQL, to drop a default constraint, the syntax is thus:
ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;
No need to name a default constraint, which probably account for bulk of constraints in SQL Server. You're right that one still have to name foreign keys & unique constraints, though. I guess it's me being a bit annoyed with being required to reference constraints explicitly as part of change scripting in the ALTER TABLEs and not appreciating the need to name constraints until just now.
Again, thanks!
In other words, you don't have to know the name of he constraint, but you need to know the column name. In MSSQL, you don't need to specify the column, just drop the constraint. Personally, I like that better because it is grouped with the constraints. Also, if I am putting together a specs doc, I don't have to separate the column defaults from other constraints. It is simply a constraint with a name.
Jared
CE - Microsoft
April 16, 2012 at 2:34 pm
Cool. Thanks for sharing your viewpoint. I can see the advantage of being able to separate constraints from columns. In my use case so far (and who know, it may change), I had no real need to reference a default constraint separately from the column it is defined for so naming it feels quite extraneous to me at the moment. You're right about needing to know the column name but that's (to my mind) more straightforward than a 2nd name.
I suppose a more flexible system is to allow both syntaxes but only for defaults since it's by definition that defaults are good only for exactly one column and a column can't have multiple defaults. There is still a need to name separately the unique and foreign key since they are not bound to only one column by definition and one column could participate in more than one UQ/FK constraint. Because of that, I already usually like to name them. Unfortunately, i wasn't doing that for defaults. At least going forward, I won't be using shortcut DEFAULT definition anymore.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply