Oracle SQL prevent unique constrain violated error

When creating a database model in your Oracle database (or any other database) you will have to creation relations. When you do things correct (in my opinion) you will base those relations on primary and foreign keys and by using those building unique constraints. Or simply put, binding tables on values. If you want to build a one to many relation you will have to make sure that you hold a column with a unique value, this will be your primary key which can not be null. Making sure that the value in this column is unique is done by the Oracle database, if you try to insert a value which is not unique the database will throw a unique constrain violated error. So you will need to provide some mechanism to ensure you only insert unique values.

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.