So, the database will prevent you from inserting a non unique value and will throw a error message, to prevent your code from entering a couple of options possible. For example one can query the highest value + 1. The downside from this is that you need to query the table before you can do a insert which results in computation which is not needed and which can be intense on large tables. a better way is to make use of a database sequence which will provide you the next value. When using a sequence you can do the insert in a couple of ways.
You can get the next value from the sequence like:
SELECT sequencename.nextval FROM dual;
you could use this as a sub-query in your insert statement or get it in your pl/sql code as a variable and use this variable in your insert statement. All options are valid and can be used in your code however an other option is available which I prefer. This option has to be considered the moment you create the table and will make use of a trigger and sequence. The trigger will fire when you insert a record into the table without the unique value. The unique value will be requested by the trigger and inserted into the table.
Before creating the table you will have to define a sequence. In the example below we first create a sequence named johan.WEBLOG_EXAMPLE_0_SEQ
CREATE SEQUENCE "JOHAN"."WEBLOG_EXAMPLE_0_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;This sequence will provide us a unique number every time we do a request for a nextval like we did in the first example and in the example below where it is done manual:
SELECT johan.WEBLOG_EXAMPLE_0_SEQ.nextval FROM dual;Now we have created a sequence we will create the table in which we will place a trigger which uses the sequence johan.WEBLOG_EXAMPLE_0_SEQ. We name the table WEBLOG_EXAMPLE_0
CREATE TABLE "JOHAN"."WEBLOG_EXAMPLE_0" ( "ID_COLUMN" NUMBER NOT NULL ENABLE, "DATA_COLUMN_0" VARCHAR2(120 BYTE), "DATA_COLUMN_1" VARCHAR2(120 BYTE), CONSTRAINT "WEBLOG_EXAMPLE_0_PK" PRIMARY KEY ("ID_COLUMN") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) TABLESPACE "USERS" ; CREATE OR REPLACE TRIGGER "JOHAN"."WEBLOG_EXAMPLE_0_TRG" BEFORE INSERT ON JOHAN.WEBLOG_EXAMPLE_0 FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.ID_COLUMN IS NULL THEN SELECT WEBLOG_EXAMPLE_0_SEQ.NEXTVAL INTO :NEW.ID_COLUMN FROM DUAL; END IF; END COLUMN_SEQUENCES; END; / ALTER TRIGGER "JOHAN"."WEBLOG_EXAMPLE_0_TRG" ENABLE;In the example above you can see that we create the table and also create a before trigger named WEBLOG_EXAMPLE_0_TRG. This will make sure that before the record is inserted it will be checked if a value for the unique column "ID_COLUMN" is given. If this value is NULL it will request a nextval from the sequence and place this in the "ID_COLUMN". after this is done the record is inserted. This is one the most secure ways of making sure you will try to insert a unique value in your "ID_COLUMN". In the example below we will see this:
INSERT INTO JOHAN.WEBLOG_EXAMPLE_0 (DATA_COLUMN_0, DATA_COLUMN_1) VALUES ('blabla', 'more blabla');when we do a select on the table we will notice that the trigger is working and that the "ID_COLUMN" column now has a value of 1, the following will get a value of 2 etc etc etc.