SQL/Oracle - "Cannot insert null into primary key"

Question

So, I have an Oracle database (in APEX) in which I have a column called "Scheme".

Like seen below:

CREATE TABLE Scheme
  (
Scheme_ID    NUMBER NOT Null,
description VARCHAR2 (800) ,
price FLOAT (3) ,
amount_sold     INTEGER ,
Personal_Trainer_ID NUMBER NOT NULL
) ;
ALTER TABLE Schema ADD CONSTRAINT Schema_PK PRIMARY KEY ( Schema_ID ) ;

Now, all my tables are set-up like this and perfectly working, but when I try an insert on my Scheme, it says I'm trying to insert null into the primary key scheme_ID.

I'll show you 2 SQL inserts I use. One for a personal_trainer, and one for the Scheme.

INSERT INTO Personal_Trainer (name, loginname, date_of_birth, password)
VALUES('Bojan', 'Bojan', '15-07-1974','fitline');

Peronal_Trainer has a Personal_Trainer_ID as primary key, exactly set-up like the Scheme. Inserting this command works perfectly fine.

insert into schema (description, price, amount_sold, Personal_Trainer_ID)
values ('3x pushups, 5x bench, 7x squats - 15kg',200, 1, 2);

Now, when I try to insert this command I get this error message:

ORA-01400: cannot insert NULL into ("SCHEME"."SCHEME_ID")

****************************EDIT*****************************************

CREATE TABLE Personal_Trainer
(
Personal_Trainer_ID NUMBER  NOT NULL,
name                VARCHAR2 (35) ,
date_of_birth       DATE ,
loginname           VARCHAR2 (35) ,
password            VARCHAR2 (35)
) ;
ALTER TABLE Personal_Trainer ADD CONSTRAINT Personal_Trainer_PK PRIMARY KEY     ( Personal_Trainer_ID ) ;

This is my table of Personal Trainer.

Answer 1

You need to provide a unique, not null, value for schemeid.

Oracle 12c allows you to elegantly define this column as an identity column:

CREATE TABLE Scheme
(
    Scheme_ID NUMBER GENERATED BY DEFAULT AS IDENTITY
    description VARCHAR2 (800) ,
    price FLOAT (3) ,
    amount_sold INTEGER ,
    Personal_Trainer_ID NUMBER NOT NULL
);

In earlier Oracle versions this option isn't available, unfortunately. The idiomatic solution would be to declare a sequence:

CREATE SEQUENCE scheme_id_seq;

And either use it directly:

INSERT INTO schema 
(scheme_id, description, price, amount_sold, Personal_Trainer_ID)
VALUES 
(scheme_id_seq.nextval, '3x pushups, 5x bench, 7x squats - 15kg',200, 1, 2);

Or create a trigger to fill it in automatically:

CREATE OR REPLACE TRIGGER schema_insert_tr
BEFORE INSERT ON schema
FOR EACH ROW
BEGIN
    IF :new.scheme_id IS NULL THEN
        SELECT scheme_id_seq.nextval INTO :new.scheme_id FROM DUAL;
    END IF;
END;