CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Serum
Serum

Reputation: 305

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

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*/

Upvotes: 5

Views: 15866

Answers (4)

RaZzLe
RaZzLe

Reputation: 2119

If you come across this topic, I would like to make some additions to previous replies.

You do not have to create an additional trigger to be associated with your primary key. First, you should create your sequence nevertheless.

CREATE SEQUENCE some_schema_name.your_sequence_name
START WITH 1
INCREMENT BY 1;

After that, all you have to do is to assign this sequence to your PK field while creating your table. For example,

CREATE TABLE some_schema_name.my_table (
ID NUMBER(8) default "some_schema_name"."your_sequence_name".nextval NOT NULL PRIMARY KEY
NAME NVARCHAR2(200)
);

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Since your Oracle database version in 11g,you need to use SEQUENCE object to increment the primary key. See documentation for CREATE SEQUENCE.

You could refer the sequence number while inserting the data. Or else, you could create a TRIGGER to increment the value in the table using sequence.NEXTVAL.

From version 12c onward, Oracle introduced IDENTITY columns which could be used as:

NUMBER GENERATED ALWAYS AS IDENTITY

On a separate note, please avoid using VARCHAR as Oracle strongly recommends to use VARCHAR2 instead to store variable-length character strings.

Upvotes: 2

thatjeffsmith
thatjeffsmith

Reputation: 22427

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.

enter image description here

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;
/

Upvotes: 6

alexherm
alexherm

Reputation: 1362

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.

Upvotes: 2

Related Questions