How can I set auto increment of a primary key in the table in SQL DEVELOPER?

Question

I have been trying to set a "w_no" to auto_increment like we do in MySQL but its been giving error.

How do we set auto increment in sql developer? Can we do by code or have to do something else?

Here is the code

CREATE TABLE ward (
      w_no      INT PRIMARY KEY AUTO_INCREMENT,
      wname     VARCHAR(30),
      w_loc     VARCHAR(30),
      phone_no  NUMERIC(10, 0),
      sno       INT
);

/*alter table ward ;*/  /*Can we do auto_increment using alter table here*/

Answer 1

Don't use VARCHAR, use VARCHAR2

Also, if you want help, try the CREATE TABLE dialogs.

We'll create the table, PK, sequence, and trigger for you.

Or, if you're on 12c or higher version of database, you can use the IDENTITY clause.

Then click on the DDL page of the wizard, we'll show you the code, so you don't have to guess what the dialog is doing.

CREATE TABLE TABLE1 
(
  COLUMN1 INTEGER NOT NULL 
, COLUMN2 VARCHAR2(20) 
, COLUMN3 VARCHAR2(20) 
, CONSTRAINT TABLE1_PK PRIMARY KEY 
  (
    COLUMN1 
  )
  ENABLE 
);

CREATE SEQUENCE TABLE1_SEQ;

CREATE TRIGGER TABLE1_TRG 
BEFORE INSERT ON TABLE1 
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.COLUMN1 IS NULL THEN
      SELECT TABLE1_SEQ.NEXTVAL INTO :NEW.COLUMN1 FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/

Answer 2

I dont think Oracle introduced IDENTITY data types until 12c. In which case you should use a SEQUENCE object in place of the lack of IDENTITY type.