CODEWITHSUNDEEP
oracle-databaseoracle11g
sim
sim

Reputation: 3602

How do I add a not null column and a check constraint in one line in Oracle 11g?

I'm trying to add a new INTEGER column to a table. The column must be NOT NULL, with a default value of 1, and only accept values greater than zero.

I'm trying to do this at the moment:

ALTER TABLE FOO_AUTHORS 
ADD PUBLICATION_PERIOD_DAYS INTEGER DEFAULT(1) NOT NULL
CONSTRAINT publicationPeriodDays 
CHECK (PUBLICATION_PERIOD_DAYS>0);

Is there a way to do this in one line? I'm following this example, but it's not working because of the NOT NULL. Is the NOT NULL then necessary?

I'm getting the following error from the DB:

QL Error: ORA-02293: cannot validate (DBOWNER.PUBLICATIONPERIODDAYS) - check constraint violated 02293. 00000 - "cannot validate (%s.%s) - check constraint violated" *Cause: an alter table operation tried to validate a check constraint to populated table that had nocomplying values.

If I try it without the NOT NULL, it works fine.

Upvotes: 0

Views: 1465

Answers (1)

MT0
MT0

Reputation: 168051

Roll the NOT NULL constraint into the CHECK constraint:

ALTER TABLE FOO_AUTHORS 
  ADD PUBLICATION_PERIOD_DAYS INTEGER DEFAULT 1
  CONSTRAINT publicationPeriodDays
  CHECK ( PUBLICATION_PERIOD_DAYS IS NOT NULL AND PUBLICATION_PERIOD_DAYS > 0 );

The existing rows will have their PUBLICATION_PERIOD_DAYS set to the default value.

Upvotes: 2

Related Questions