IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 1 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Altering unnamed constraints
Let's say I create a table with a constraint on the column where the constraint remains nameless:
\nCREATE TABLE mytable(\n   mychar  CHAR(1) NOT NULL CHECK(mychar IN('X','Y'))\n)\n

This sytax checks out to be [link|http://developer.mimer.com/validator/index.htm|SQL-99 compliant] and works on both Oracle and SQLServer. Problem I have is if I want to drop or modify the constraint - let's say I want to add 'Z' to the list - I'm not sure of the syntax for ALTER TABLE that allows you to modify a constraint that doesn't have a name. In the past, I just looked up in the dictionary and found the name assigned by the database, but it's just random.

Is there a way to alter the constraint? Or should I follow some advice I stumbled across that says you should name every constraint? (I find that I already have enuf trouble naming things, and it's just one more to keep track of).

Thanks
New No other way than by name, AFAIK, in Oracle.
Unless you use some handy client tool like TOAD, that is, where you can easily see the constraint text in the "Constraints" tab of the right-hand pane in the schema browser; then you can administer them from there and pretty much disregard the system-assigned constraint names in the left-most column... But it's there nonetheless, and AFAICS it has to be what TOAD uses, "behind the scene"(*), to do the same thing you used to do manually.

Also, if you only ever change your tables by dropping and re-creating them, then you can of course just alter the (anonymous) table-creation script you've saved somewhere and re-run it. This is where the copy-alter-restore technique comes in handy: "CREATE TABLE MY_TABLE_TEMP AS SELECT * FROM MY_TABLE; <<run your table-creation script (drop MY_TABLE first, if that isn't in the script)>> INSERT INTO MY_TABLE (SELECT * FROM MY_TABLE_TEMP); DROP TABLE MY_TABLE_TEMP;". That's actually how I often do it on development boxes, when I don't have TOAD available; unless the structure has changed too much in terms of added / dropped / renamed columns, that is... But for stuff like check constraints, it works fine.

But, apart from these "special cases" / personal technique preferences / workarounds, the general rule of thumb is, YES, you *should* name everything, including constraints.

HTH!


   [link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Resident [link|http://z.iwethey.org/forums/render/content/show?contentid=119792|zIWETHEY pilkunnussija]


(*): Or is that "behind the scenes", plural? I think that's the form I've seen most often... But it doesn't really make any sense! Neither does the whole idea of using the word "scene" in this expression, BTW -- unless you realise that this is a remnant of an older usage, showing that in English too, as is still the case in many other languages, "scene" once used to mean not *what* you stage on a stage, but the stage you stage it *on*! (cf Fr. "scène", Ger. "Szene", and Swe. "scen", which all mean what modern English calls a "stage".) Anyway, if you see the expression as originally a theatrical analogy, then it really only makes sense to talk about one single scene (="stage") at a time; what is meant is the back-stage machinery of changing backdrops, "deus ex machina" appearances, etc, for ONE play -- NOT some plural-stage thing... What would a three-ring-circus have to do with anything?!?
New Deus Ex TOAD
I may try to put together some dynamic SQL to trundle through the dictionary and find the name assigned to the constraint - allowing me to drop and replace the constraint. I think I know how to do it in SQLServer but I'll have to do a little research on how Oracle stores the stuff in the data dictionary.

As for "behind the scenes", finding a web reference to the origin of a common phrase is somewhat difficult because it shows up in so many places (though I did stumble across [link|http://freaky_freya.tripod.com/Drunktionary/drunkcentral.html|The Drunktionary] as a result that may come in handy some time down the road).

My speculation would be that it is based on theatre and refers to events that occur off stage (not having computer generated special effects back in Elizabethean times, they had to use their imagination). I'd think that it's plural because all the scenes themselves are usually in place when the play starts - something like a canvas or a backdrop. So when the scene changes, usually what happens is the front curtain is lowere and one scene is rolled up to expose the next scene. Hence, the stage has multiple scenes in place, though the audience is only viewing one at a time.
     Altering unnamed constraints - (ChrisR) - (2)
         No other way than by name, AFAIK, in Oracle. - (CRConrad) - (1)
             Deus Ex TOAD - (ChrisR)

Ph34r the triple-recursive meta-LRPD.
78 ms