5

I have a table. I need to add a NOT NULL constraint to one of the columns. This is my best attempt at a solution:

alter table clookup add CONSTRAINT lookup_9 NOT NULL (clookup_col)

This produces ERROR at line 1: ORA-00904: : invalid identifier on the NOT NULL part. Why?

  • 1
    This is such a basic syntax error. The documentation tells you which format to use. Voting to close this as too localized. – Colin 't Hart Jun 4 '18 at 9:54
  • @Colin'tHart Oracle Syntax description is often rather comprehensive and NOT NULL is very Special because you cannot add the constraint in the usual waay but have to alter the column as shown in the answer. So I think this is a reasonable question. – miracle173 Jun 4 '18 at 12:52
  • @miracle173: Glad it's not just me who thinks the way Oracle handles this doesn't make sense. – moonman239 Jun 6 '18 at 4:11
8

Oracle 12c:

Table

create table clookup ( clookup_col varchar2( 64 ) );

Add a NOT NULL constraint

alter table clookup
modify ( clookup_col constraint lookup_9 not null ) ;

Optional

select 
  table_name
, constraint_name
, constraint_type
from user_constraints
where table_name = 'CLOOKUP' ;

-- result
TABLE_NAME  CONSTRAINT_NAME  CONSTRAINT_TYPE  
CLOOKUP     LOOKUP_9         C 

Addendum

It may not always be necessary to use the ALTER TABLE ... MODIFY ... technique. There is a "special rule" for defining NOT NULL constraints (see documentation):

You can define constraints syntactically in two ways:

As part of the definition of an individual column or attribute. This is called inline specification.

As part of the table definition. This is called out-of-line specification.

NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.

Also (here):

Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition.

The following examples show what does NOT work: just adding a NOT NULL constraint (not a column) to a table, via ALTER TABLE ... ADD ... (out-of-line).

-- Oracle 12c
create table T ( name varchar2( 32 ) ) ;

alter table T 
add constraint name_nn not null ( name ) ;
-- ORA-00904: : invalid identifier

alter table T
add ( 
  n number
, constraint name_nn not null ( name )  -- out-of-line
) ;
-- ORA-00904: : invalid identifier

ALTER TABLE ... ADD ... can be used for other constraints eg UNIQUE or PRIMARY KEY "out-of-line" (as in the examples above), though. It also works when we want to add NOT NULL constraints "inline" ie when specifying a column and a constraint eg

create table T ( name varchar2( 32 ) ) ;

alter table T
add n number not null ;
-- Table T altered

-- or
alter table T
add n number constraint number_nn not null ;
-- Table T altered
  • One weird thing (I found) about eg CHECK ( name not null ) is: when executing DESCRIBE, then the name column is reported "nullable". When having a NOT NULL in place (instead of a check), DESCRIBE reports the column as NOT NULL. – stefan Jun 7 '18 at 6:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.