jcon.no: Oracle blog

I often see DDLs with NOT NULL definitions which is really redundant. When I see such DDLs I really question “do the writer really know what he or she is doing”. They might not hurt you application, but why set them if they are redundant? (If you know I reason please comment)

Example:

CREATE TABLE post (
   id NUMBER(19,0) GENERATED ALWAYS AS IDENTITY NOT NULL ,
   title VARCHAR2(255 CHAR),
   version NUMBER(10,0) NOT NULL,
   PRIMARY KEY (id));

In this case I can see two reasons why I would NOT define the ID column with a NOT NULL constraint.

The first reason is that this column is also defined as an PRIMARY KEY. Primary keys are per definition NOT NULL columns.
When doing this you’ll have two constraints in your database checking the NOT NULL constraints:

col constraint_name for a15
col constraint_type for a5
col search_condition for a30
select constraint_name, constraint_type type, search_condition from user_constraints where table_name = 'POST';

CONSTRAINT_NAME T SEARCH_CONDITION              
--------------- - ------------------------------
SYS_C0053347    C "ID" IS NOT NULL              
SYS_C0053348    C "VERSION" IS NOT NULL         
SYS_C0053349    P

Both the SYS_C0053347 (NN) and SYS_C0053349 (P) constraint will enforce NOT NULL. I have no evidence that this really creates extra work in Oracle, but there is NO need for this extra constraint. Why should you then define it?
Note! I’ll also comment on the system generated names (see further down).

The second reason for NOT NULL being redundant is the IDENTITY definition. This makes it impossible to insert a NULL value in the column (as far as I know … please prove me wrong):

INSERT INTO post (id, title, version) values (null, 'Test',1);

SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.

Another issue is the IDENTITY column by it selves. I never ever used them. Instead a use a explicit sequence an set is as the DEFAULT value. You can not drop the sequence generated for an IDENTITY column without either dropping the column or the table itselves. I have had cases where the table was dropped, and the identity column remained, and it was impossible to remove it. So I do not use them … period.

The last issue I have with this DDL is the system generated names. I ALWAYS define names for constraints – even NOT NULL constraints, so my DDL would be something like this (or actually two):

CREATE SEQUENCE post_seq START WITH 1 INCREMENT BY 1 CACHE 100;

CREATE TABLE post (
   id       NUMBER(19,0)         DEFAULT post_seq.nextval,
   title    VARCHAR2(255 CHAR),
   version  NUMBER(10,0)         CONSTRAINT post_version_nn NOT NULL,
   CONSTRAINT post_pk PRIMARY KEY (id)
);

But that’s me … 🙂