8 Replies Latest reply: Jul 26, 2011 3:59 PM by 872530 RSS

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

    raindog
      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...