Stop doing it. Watch.
CREATE TABLE tAs HillbillyToad said,
(
id NUMBER
CONSTRAINT pk_id PRIMARY KEY
);
SH@I_HAVE_NO_IDEA>INSERT INTO t ( id ) VALUES ( 1 );
1 row created.
Elapsed: 00:00:00.33
SH@I_HAVE_NO_IDEA>INSERT INTO t ( id ) VALUES ( NULL );
INSERT INTO t ( id ) VALUES ( NULL )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SH"."T"."ID")
It is better than no constraint, that's for sure. The heart was in the right place...
Out of habit, I suppose, I always define both constraints in my deployment scripts for an application's schema.
ReplyDeleteI 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.
But it is a little silly.
Oh, no, no, no. I disagree. Completely.
ReplyDeleteFirstly it depends how your script your table creations.
I never have the primary/foreign key definitions inline with my table.
And I would argue that your column definition is incomplete:
CREATE TABLE t( id NUMBER CONSTRAINT pk_id PRIMARY KEY);
should be
CREATE TABLE t( id NUMBER NOT NULL CONSTRAINT pk_id PRIMARY KEY);
And this creates the NOT NULL constraint.
I would argue that the NULL-ness of your column should be independent of whether it is a PRIMARY KEY or not.
Otherwise, if for whatever reason you disable your primary key, you lose your not null protection.
Hi.
ReplyDeleteAnother 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.
Cheers
Tim...
@DomBrooks - I fully realize that both constraints (uniqueness and not null) are definitely two different propositions and defining both is not a silly idea.
ReplyDeleteThe 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.
My habit of defining the not null constraint was actually borne out of the draft development process of the schema itself.
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.
@oraclenude - yep, wasn't disagreeing with your comment - I agree with defining both.
ReplyDeleteWas commenting on what Chester Chetster said.
If you really want to be efficient, then
ReplyDeleteCREATE TABLE t( id NUMBER PRIMARY KEY);
is enough.
Cheers,
Colin
@DomBrooks
ReplyDeleteA very long time ago I posted on inline vs. out-of-line constraints...my preference is inline.
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).
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.
chet
@Tim
ReplyDeleteAgreed, tools do tend to do that.
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.
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).
@Colin
ReplyDeleteIt'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).
If you can name it something meaningful, it's helpful down the road, IMO.
chet
I remember that post. Just went back to look at it.
ReplyDeleteI commented on it. Seems like at the time I was in an inline phase.
Clearly now I have abandoned that muddled commuinist thinking and implicit not nulls, and out of line constraints are my preference.
> If you can name it something meaningful, it's helpful down the road, IMO
Don't really agree anymore.
It depends.
Tables, columns, packages - oh sure, without a doubt. Have to be meaningful.
But, over time, I've definitely abandoned meaningful names for constraints and indexes. It's not worth it.
Double standards? I don't think so.
The intention of a table, column etc should always be unambiguous.
The particular way you have packaged up code - if there is a specific intent - should also be clear.
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.
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.
Aversons to system-generated names? We all have our personal bugbears...
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.
For me, I much prefer a counter: i_table_name_or_alias_01
pk_table_name_or_alias
fk_table_name_or_alias_01ck_table_name_or_alias_01
@dombrooks
ReplyDeleteBlasphemy! :)
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 not system generated.
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).
ReplyDeleteSo 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...
@Gary
ReplyDeleteI knew there was a reason! :)
But you've never told me why? (or I was too lazy to figure it out.)
ReplyDeleteFor one, I don't like inline PK constraints, that's just a style thing :)
But I do like this
CREATE TABLE nerd (
my_pk VARCHAR2(100) CONSTRAINT nerd$my_pk_nn NOT NULL
);
ALTER TABLE nerd ADD CONSTRAINT nerd_pk PRIMARY KEY (my_pk) USING INDEX;
@Bradd
ReplyDeleteFirst 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.
There, how's that for some style? :)
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.
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!).
chester