ORA-00984: column not allowed here while inserting data excluding the sequence trigger

First I created table using this query:-

CREATE TABLE mca (
    id number not null primary key,
    name varchar2(200) not null,
    password varchar2(200) not null,
    email varchar2(200) not null,
    country varchar2(200) not null
)

Then I created sequence using the query:-

CREATE SEQUENCE id_seq
 START WITH     1
 INCREMENT BY   1

Then I created Trigger so that id would auto increment during insertion:-

CREATE OR REPLACE TRIGGER MCA_TRIG
BEFORE INSERT ON MCA
FOR EACH ROW
  WHEN (new.ID IS NULL)
BEGIN
  :new.ID := ID_SEQ.NEXTVAL;
END;

Now when I’m trying to insert data, I’m getting error:-

insert into mca (name,password,email,country) values ("Ankit", "Ankit123", "[e-mail]","India");

Error- Column not allowed here.

If I try this query:-

insert into mca values ("Ankit", "Ankit123", "[e-mail]","India");

Error- Not enough Values.

I also tried to add id_sec.NEXTVAL in Values still getting column not allowed error.

NOTE- I am using Oracle 11g

Answer

Error- Column not allowed here.

You also need to use ' single quotes for string literals (double quotes are used to signify case-sensitive identifiers, such as column names).

Error- Not enough Values.

You have 5 columns but only 4 values in the INSERT statement and you haven’t told the SQL parser which 4 columns you want to use. Give the column names.

So your statement should be:

insert into mca (name,password,email,country)
values ('Ankit', 'Ankit123', '[e-mail]','India');

(Don’t store the password as plain text. At the very least, store a one-way hash of it.)

CREATE TABLE mca (
    id            NUMBER
                  -- NOT NULL           Not necessary for a primary key
                  CONSTRAINT mca__id__pk PRIMARY KEY,
    name          varchar2(200) not null,
    password_hash varchar2(200) not null,
    password_salt varchar2(200) not null,
    email         varchar2(200) not null,
    country       varchar2(200) not null
);

CREATE SEQUENCE mca_id_seq;

And the triggers:

CREATE TRIGGER mca_id
BEFORE INSERT ON MCA
FOR EACH ROW
BEGIN
  :new.ID := mca_id_seq.NEXTVAL;
END;
/

and:

CREATE TRIGGER mca_hash_and_salt_password
BEFORE INSERT OR UPDATE ON MCA
FOR EACH ROW
BEGIN
  IF :new.PASSWORD_HASH = :old.PASSWORD_HASH THEN
    -- Assume things haven't changed (The chances of a hash collision are vanishingly small).
    -- Make sure the old salt is not replaced if the password hash hasn't changed.
    :new.PASSWORD_SALT := :old.PASSWORD_SALT;
  ELSE
    -- Regenerate a new salt and hash the password.
    :new.PASSWORD_SALT := DBMS_RANDOM.STRING( 'P', FLOOR( DBMS_RANDOM.VALUE( 40, 61 ) ) );
    SELECT STANDARD_HASH ( :new.PASSWORD_SALT || :new.PASSWORD_HASH, 'SHA512' )
    INTO   :new.PASSWORD_HASH
    FROM   DUAL;
  END IF;
END;
/

Then:

insert into mca (
  name,
  password_hash,
  email,
  country
) values (
  'Ankit',
  'Ankit123',
  '[e-mail]',
  'India'
);

Gives the value in the table:

ID | NAME  | EMAIL           | COUNTRY | PASSWORD_SALT                                           | PASSWORD_HASH                                                                                                                   
-: | :---- | :-------------- | :------ | :------------------------------------------------------ | :-------------------------------------------------------------------------------------------------------------------------------
 1 | Ankit | [e-mail] | India   | &!WAMmJkSpQgUD(BS~ub+2*Yk]]bT_IA* xm|:.[oEz.)*u*HAEV*B | 82CF5AE586605968DA320A64E7DDC7154FD11BEF0E0680350CA9BF5D5BEEB65D8D05FF50B8DC061E698A94FDAED46A73BAD826303C90AB49352E869931DCF04E

db<>fiddle here