plsql - How do you implement auto incremented sequence values in Oracle PL/SQL? -

in oracle database table, how auto incremented sequence values done pl/sql such key value or id typed columns?

included discussion on sizing table resources based on know projected growth of related tables in given schema. how many piano tuners there in city of chicago? less population of city altogether... :) , on.

know data. 

how do that? read on.

using database triggers auto increment column values

one possible approach use database triggers. values ensured unique through implementation of sequence typed database objects.

in example above, table fundraiser has associated sequence called fundraiser_seq primary key value.

the parts list: need example.

create table  "fund_people"     (    "person_id" number not null enable,          "anonymous_ind" varchar2(1) not null enable,          "alias_ind" varchar2(1) not null enable,          "alias_name" varchar2(50),          "first_name" varchar2(50),          "last_name" varchar2(50),          "phone_number" varchar2(20),          "email_address" varchar2(100),        constraint "fund_people_pk" primary key ("person_id") enable    ) ; 

this older sample of code, the

select ... dual 

construct did older releases of oracle database. release 11g changes sequence increments can assigned directly pl/sql variable or sql statement call. both work.

challenge figure out revised pl/sql might like...

create or replace trigger  "bi_fund_people"    before insert on "fund_people"                  each row   begin      if :new."person_id" null      :new."person_id" := "fund_people_seq".nextval;    end if;  end;  

the notation is:

1. "bi" before insert
2. :new value of column value after event.

fund_id column ignored in insert statement. starting value of each sequence 20. call seq.nextval value increments sequence amount identified in sequence declaration.

a brief discussion on sequence sizing

if spec sequence without maximum value, system assigns largest possible integer handled database, 20+ digits long.

now going table contains records numbering twenty or more orders of magnitude: "9999999999999999999999999999"?

hmmmm... i've heard of "defensiveness in coding", applications, is... in defensible?

consider looking @ original project design have worked on google code: google code project: fundraiser organizer

this rough relation in record counts between each entity in schema set up. 2 circles metrics proportional values calculate or aggregate. considering data cases, relative magnitudes each count of records?

a @ first screenshot shows estimates. project started out sheet of data keep track of fund raising drive. hope find informative , thought inspiring!

the ui , testing efforts conducted on public demo site hosted oracle @ oracle apex demo system