CODEWITHSUNDEEP
sqloracle-databaseinsertprimary-keyreferential-integrity
Bhetzie
Bhetzie

Reputation: 2922

SQL Oracle Determining Primary Key Values

In Oracle SQL what is the best way to create primary key values for an entity? I have been adding 100 for each different entity and incrementing new entities by 1, but I can see how this is not good because if I have over 100 inserts into a table I would reuse a primary key number. I have many tables with primary keys, how do I determine a way to make sure all of the values are unique and there is no chance of them overlapping with other primary key values? An example of what I have been doing is as follows:

create table example (
foo_id number(5);
Constraint example_foo_id_pk Primary key (foo_id);

Insert Into example
Values(2000);
Insert Into example
Values(2010);



create table example2 (
   foobar_id number(5);
   Constraint example2_foobar_id_pk Primary key (foobar_id);

   Insert Into example2
   Values (2100);
   Insert Into example2
   Values (2110);

Upvotes: 0

Views: 1098

Answers (3)

GolezTrol
GolezTrol

Reputation: 116110

In Oracle people commonly use sequences to generate numbers. In an insert trigger, the next value of the sequence is queried and put in the primary key field. So you normally don't pass a value for that field yourself.

Something like this:

CREATE SEQUENCE seq_example;

CREATE OR REPLACE TRIGGER tib_example
BEFORE INSERT ON example 
FOR EACH ROW
BEGIN
  SELECT seq_example .NEXTVAL
  INTO :new.foo_id
  FROM dual;
END;
/

Then you can just insert a record without passing any value for the id, only for the other fields.

If you want the keys to be unique over multiple tables, you can use the same sequence for each of them, but usually this is not necessary at all. A foo and a bar can have the same numeric id if they are different entities.

If you want every entity to have a unique ID throughout your database, you might consider using GUIDs.

Upvotes: 2

Drunix
Drunix

Reputation: 3343

Starting with Oracle database 12C, you can use identity columns. Use something like

foobar_id number(5) GENERATED BY DEFAULT ON NULL AS IDENTITY

For older versions sequences are the recommended way, although some ORM tools offer using a table which stores the counter. Inserting via sequence can be done either with triggers or by directly inserting sequence.nnextval into your table. The latter may be useful if you need the generated ID for other purposes (like inserting into child tables).

Upvotes: 0

Kelly
Kelly

Reputation: 995

Try using a sequence..

CREATE SEQUENCE Seq_Foo
   MINVALUE 1
   MAXVALUE 99999999
   START WITH 1
   INCREMENT BY 1;

To use the sequence in an insert, use Seq_Foo.NextVal.

Upvotes: 0

Related Questions