SQL & PL/SQL

I like to keep things neat. It's probably a personality disorder.

Let's say I create a table:

create table t (
id integer,
first_name varchar2(30),
last_name varchar2(30)
);

Now I add a primary key constraint:

alter table t add constraint t_pk primary key (id);

When I look in user_constraints or user_cons_columns, the constraint is named "t_pk" and it is pleasing to me.

However...you can't ALTER TABLE...ADD CONSTRAINT" for "NOT NULL" constraints:

SQL> alter table t add constraint t_first_name_nn not null (first_name);
alter table t add constraint t_first_name_nn not null (first_name)
*
ERROR at line 1:
ORA-00904: : invalid identifier

I've researched a little and while this works...

ALTER TABLE t MODIFY first_name NOT NULL;

...you end up with a name like 'SYS_C0011893' for the constraint. There's apparently no way to name that constraint in the DDL.

That strikes me as odd - I can name any other constraint I put on a table/column except NOT NULL? I'm sure I'm doing something wrong...