SQL Oracle Determining Primary Key Values

Question

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

Does this code actually work? – Jan Peter Mar 20 '14 at 20:59 — Jan Peter, Mar 20 '14 at 20:59
It's an example, but yes it should work – Bhetzie Mar 20 '14 at 23:38 — Bhetzie, Mar 20 '14 at 23:38

GolezTrol · Answer 1 · 2014-03-20T21:05:29.327

2

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.

edited Mar 20 '14 at 21:05

answered Mar 20 '14 at 20:58

GolezTrol

114,394
18
182
210

I actually don't want to use sequences because I have some composite keys that are both the primary and foreign key in a table. – Bhetzie Mar 20 '14 at 23:38
If a key is both primary and foreign, that means it basically has a 1:1 relationship (or 0:1) to the parent table. If you do that, then you will have a parent record for which you are inserting children, and you can just use the id of the parent. Don't create a trigger + sequence for this specific child table. – GolezTrol Mar 21 '14 at 08:56
I guess I was asking what the convention for assigning primary key values is without using sequences, but from the examples it seems like it doesn't matter much – Bhetzie Mar 21 '14 at 14:59
I didn't understand from your question that you explicitly didn't want to use sequences. But if you don't well, use GUIDs, or generate your own number. The biggest problem with the latter is that you have to make sure it's unique. You could do that by saving the last ID in a table, and get a new ID by doing something like `UPDATE MyKeyTable SET Id = Id + 1 WHERE Target = 'invoice' RETURNING Id INTO YourNewKeyID`. If you execute that from a program block, you can update the ID and get its new value into a variable in one go, without risking conflicts with other sessions. – GolezTrol Mar 21 '14 at 16:01

score 0 · Answer 2 · answered Mar 20 '14 at 21:00

0

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.

answered Mar 20 '14 at 21:00

Kelly

945
2
18
31

The issue is that I actually have many tables and some are both primary and foreign keys so a sequence would not work for these tables – Bhetzie Mar 20 '14 at 23:40

score 0 · Answer 3 · answered Mar 20 '14 at 21:03

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).

SQL Oracle Determining Primary Key Values

3 Answers3