0

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

via
  • 40
  • 6

1 Answers1

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • ORA-00936: missing expression – via Nov 11 '20 at 10:52
  • Still missing expression, when i execute ALTER TABLE book MODIFY status DEFAULT ON NULL 1; – via Nov 11 '20 at 10:53
  • @SilviaNovita Please check the db<>fiddle, as it works without errors. Also, please note the database version requirements for that syntax. – MT0 Nov 11 '20 at 10:55