tag:blogger.com,1999:blog-8884584404576003487.post3963621613797373493..comments2024-02-29T09:43:12.251-05:00Comments on ORACLENERD: PRIMARY KEY and NOT NULLoraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-8884584404576003487.post-30181184108318135232010-02-11T22:56:24.598-05:002010-02-11T22:56:24.598-05:00@Bradd
First of all, your first parenthesis shoul...@Bradd<br /><br />First of all, your first parenthesis should be on the second line, not following the CREATE TABLE on the first. Secondly, you should have a hard return after the data type declaration (DEFAULT can be on this line though) and then the CONSTRAINT declaration should be indented exactly 2 (two) spaces under the column definition.<br /><br />There, how's that for some style? :)<br /><br />I get the out-of-line constraints, I just don't like them. I want to be able to "read" my table and all it's parts...not have to scroll down the page to see if there is an ALTER TABLE to add a PK. Same goes for the others I suppose.<br /><br />I'm sure the majority of "my" style comes down to "hand-writing" my tables. I never, ever generate them...they're beautifully (arguable) hand-crafter works of art...like a good beer (that should get you!).<br /><br />chesteroraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-72595062454177115292010-02-11T22:50:29.397-05:002010-02-11T22:50:29.397-05:00But you've never told me why? (or I was too la...But you've never told me why? (or I was too lazy to figure it out.)<br /><br />For one, I don't like inline PK constraints, that's just a style thing :)<br /><br />But I do like this<br /><br />CREATE TABLE nerd (<br /> my_pk VARCHAR2(100) CONSTRAINT nerd$my_pk_nn NOT NULL<br />);<br /><br />ALTER TABLE nerd ADD CONSTRAINT nerd_pk PRIMARY KEY (my_pk) USING INDEX;Bradd Piontekhttps://www.blogger.com/profile/09812125551238871609noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-38936934098108174732010-02-08T16:29:15.981-05:002010-02-08T16:29:15.981-05:00@Gary
I knew there was a reason! :)@Gary<br /><br />I knew there was a reason! :)oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-71616009525962604262010-02-08T16:28:32.979-05:002010-02-08T16:28:32.979-05:00My aversion to system generated names is if you ne...My aversion to system generated names is if you need to alter them (eg disable a constraint or drop an index for a bulk load). A system generated name will often be different between dev/test and prod (unless they have been created from backups).<br />So you either have to tweak the dev/test code to run on prod or get tricky and dynamically get the constraint/index name from the data dictionary...SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-35503121822684635092010-02-08T09:50:18.037-05:002010-02-08T09:50:18.037-05:00@dombrooks
Blasphemy! :)
I see your point thoug...@dombrooks<br /><br />Blasphemy! :)<br /><br />I see your point though. Perhaps my preference is due to the fact that I never seem to be in a maintenance phase, which may make a difference. By that I mean staring at the same stuff over and over...if it doesn't have a "real" meaning (i.e. columns/tables/etc) then it doesn't really matter what it's called (as long as it's <i><b>not</b></i> system generated.oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-91136002151016078932010-02-08T09:45:17.466-05:002010-02-08T09:45:17.466-05:00I remember that post. Just went back to look at it...I remember that post. Just went back to look at it.<br />I commented on it. Seems like at the time I was in an inline phase. <br /><br />Clearly now I have abandoned that muddled commuinist thinking and implicit not nulls, and out of line constraints are my preference.<br /><br />> If you can name it something meaningful, it's helpful down the road, IMO<br /><br />Don't really agree anymore.<br />It depends. <br /><br />Tables, columns, packages - oh sure, without a doubt. Have to be meaningful.<br /><br />But, over time, I've definitely abandoned meaningful names for constraints and indexes. It's not worth it.<br /><br />Double standards? I don't think so.<br /><br />The intention of a table, column etc should always be unambiguous.<br />The particular way you have packaged up code - if there is a specific intent - should also be clear.<br /><br />But with indexes and constraints, all too often you see the original name lost touch with what it does over time, due to extra conditions on the check or extra trailing columns on the index, etc.<br /><br />I think it's pointless in the long run, and can even waste more time if things have deviated from original meaning and you start making assumptions.<br /><br />Aversons to system-generated names? We all have our personal bugbears...<br /><br />Name it simply and you won't have to keep going back to look at the name or using copy & paste and you won't read in any meaning which you shouldn't.<br /><br />For me, I much prefer a counter: i_table_name_or_alias_01<br />pk_table_name_or_alias<br />fk_table_name_or_alias_01ck_table_name_or_alias_01DomBrookshttps://www.blogger.com/profile/02598622186013843759noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-30101438982623286242010-02-07T22:53:27.386-05:002010-02-07T22:53:27.386-05:00@Colin
It's not necessarily about brevity...i...@Colin<br /><br />It's not necessarily about brevity...it's just me trying to be clear in my intentions (besides, I hate system generated names so I wouldn't allow that CREATE TABLE statement to fly).<br /><br />If you can name it something meaningful, it's helpful down the road, IMO.<br /><br />chetoraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-52610651339900132402010-02-07T22:52:23.227-05:002010-02-07T22:52:23.227-05:00@Tim
Agreed, tools do tend to do that.
I however...@Tim<br /><br />Agreed, tools do tend to do that.<br /><br />I however seem to have to write up my tables by hand. I'm not saying it's smart or anything, it's just the way I have done things.<br /><br />Part of it is, I guess, included with my testing. I have scripts to build and teardown my entire application (schema) that I constantly build upon (until I am too lazy to maintain it any longer).oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-23474479310411930722010-02-07T22:50:32.446-05:002010-02-07T22:50:32.446-05:00@DomBrooks
A very long time ago I posted on inlin...@DomBrooks<br /><br />A very long time ago I posted on <a href="http://www.oraclenerd.com/2007/09/today-one-of-my-more-feisty-colleages.html" rel="nofollow">inline vs. out-of-line</a> constraints...my preference is inline.<br /><br />I wouldn't add that particular NOT NULL constraint because I don't like that the name is system generated (though perhaps you just left it out for brevity's sake).<br /><br />I think if, for whatever reason, I did remove the PK from a table (with it's implicit NOT NULL), I would do so knowing that it could then be succeptable to NULLs being entered. If I were to do that...it would have to be during a maintenance window at the very least.<br /><br />chetoraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-35874015964739236122010-02-04T14:31:33.966-05:002010-02-04T14:31:33.966-05:00If you really want to be efficient, then
CREATE T...If you really want to be efficient, then<br /><br />CREATE TABLE t( id NUMBER PRIMARY KEY);<br /><br />is enough.<br /><br />Cheers,<br /><br />ColinColin 't Harthttps://www.blogger.com/profile/15623835285718803326noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-78355993897232004702010-02-04T13:16:43.579-05:002010-02-04T13:16:43.579-05:00@oraclenude - yep, wasn't disagreeing with you...@oraclenude - yep, wasn't disagreeing with your comment - I agree with defining both.<br /><br />Was commenting on what Chester Chetster said.DomBrookshttps://www.blogger.com/profile/02598622186013843759noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-32728424939793155922010-02-04T11:25:26.040-05:002010-02-04T11:25:26.040-05:00@DomBrooks - I fully realize that both constraints...@DomBrooks - I fully realize that both constraints (uniqueness and not null) are definitely two different propositions and defining both is not a silly idea.<br /><br />The reason I say that it is a little silly is that I live in a world where the primary key of production data is never disabled, so having the not null constraint defined is essentially moot.<br /><br />My habit of defining the not null constraint was actually borne out of the draft development process of the schema itself.<br /><br />I find that it is the exception, not the rule, that my columns don't have a not null constraint. I view every column that is alright with null values suspiciously with my developer's eye. Also, if for some reason the primary key were to morph into something different (during the original draft of the schema), having the not null defined ensures that the redefined primary key hasn't left me with a column with unintended allowed nulls.Clever Idea Widgetryhttps://www.blogger.com/profile/11224068405843575576noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-64791750086045267842010-02-04T09:10:59.841-05:002010-02-04T09:10:59.841-05:00Hi.
Another thing to consider, some tools either ...Hi.<br /><br />Another thing to consider, some tools either set the this constraint for you, or force you to specify it when defining the primary key. If I remember correctly, Oracle Designer only allows you to select NOT NULL columns when defining a primary key.<br /><br />Cheers<br /><br />Tim...Tim...https://www.blogger.com/profile/17721555946005999179noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-78421357124414688872010-02-04T04:39:22.931-05:002010-02-04T04:39:22.931-05:00Oh, no, no, no. I disagree. Completely.
Firstly i...Oh, no, no, no. I disagree. Completely.<br /><br />Firstly it depends how your script your table creations.<br /><br />I never have the primary/foreign key definitions inline with my table.<br /><br />And I would argue that your column definition is incomplete:<br /><br />CREATE TABLE t( id NUMBER CONSTRAINT pk_id PRIMARY KEY);<br /><br />should be <br /><br />CREATE TABLE t( id NUMBER NOT NULL CONSTRAINT pk_id PRIMARY KEY);<br /><br />And this creates the NOT NULL constraint.<br /><br />I would argue that the NULL-ness of your column should be independent of whether it is a PRIMARY KEY or not.<br /><br />Otherwise, if for whatever reason you disable your primary key, you lose your not null protection.DomBrookshttps://www.blogger.com/profile/02598622186013843759noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-46243026844501746662010-02-04T00:13:04.474-05:002010-02-04T00:13:04.474-05:00Out of habit, I suppose, I always define both cons...Out of habit, I suppose, I always define both constraints in my deployment scripts for an application's schema. <br /><br />I think I do this because the data dictionary query on a column's nullness is Y unless the not null constraint is specifically defined - even on a column that is a member of the primary key. <br /><br />But it is a little silly.Clever Idea Widgetryhttps://www.blogger.com/profile/11224068405843575576noreply@blogger.com