Missing Expression when Alter Default on NULL 1

Question

I have column in table book... named as status... i want to set default 1...

but, i tried DEFAULT 1, ALTER TABLE book MODIFY status DEFAULT 1 and i insert new record... the record status is null

i tried to use Default on null 1

ALTER TABLE book MODIFY status DEFAULT ON NULL 1;

the output : ORA-00936: missing expression

what should i do? if i want the status value is default 1, not null when inserting new record

Answer 1

Your ALTER TABLE book MODIFY status DEFAULT ON NULL 1; statement works (from Oracle 12.0.1 onwards):

CREATE TABLE book (
  id     INT
         GENERATED ALWAYS AS IDENTITY
         PRIMARY KEY,
  status NUMBER(1)
);

ALTER TABLE book MODIFY status DEFAULT ON NULL 1;

INSERT INTO book ( status ) VALUES ( NULL );
INSERT INTO book ( status ) VALUES ( 0 );
INSERT INTO book ( status ) VALUES ( 1 );

Then:

SELECT * FROM book;

Outputs:

ID | STATUS
-: | -----:
 1 |      1
 2 |      0
 3 |      1

db<>fiddle here

---

If you are using a database version before 12.0.1 then, alternatively, you can use a trigger:

CREATE TRIGGER book__status_is_null__trg
BEFORE INSERT OR UPDATE ON book FOR EACH ROW
BEGIN
  IF :new.status IS NULL THEN
    :new.status := 1;
  END IF;
END;
/

db<>fiddle here