This discussion is archived
8 Replies Latest reply: Jul 26, 2011 1:59 PM by 872530 RSS

Possible to name a NOT NULL constraint (instead of SYS_C00xxxx)?

raindog Newbie
Currently Being Moderated
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...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points