Re: Convert Mysql table schema to Oracle table

"Jon" <jonathan.p.crawford_at_gmail.com> wrote in message news:1143134751.705075.326190_at_j33g2000cwa.googlegroups.com...
: Oracle doesn't have automatic identies like Microsoft SQL. Whomever is
: inserting the data into the table is still responsible for inserting a
: valid key and keeping track of what is 'valid'. There are a couple of
: ways to do it. The easy way is to "select max(id) from builds" and then
: increment the result of the scalar select.
:

never do it this way -- it does not guarantee a unique id, as two or more users can grab the same max and attempt to insert the same (uncommitted) record, resulting in duplicates or index violations

: The better way to do it requires more work.
:
:
: You have to create a sequance first.
: CREATE SEQUENCE SEQ_BUILD_IDS INCREMENT BY 1 START WITH 1000;
:
: Then before you do an insert you
: "SELECT SEQ_BUILD_IDS.NEXTVAL FROM DUAL"
:
: You then use the result of that query as the value for the id field
: since you seem like you want that to be a self increment key value.
:

this NEXTVAL syntax is correct, but the only reason to do a select from dual to get the sequence's next value is if you have built it into a trigger (which takes the responsibility from the user and gives it to the database, where it belongs), which is one of two correct ways to use a sequence:

SQL> create table t1 (
  2 id number constraint pk_t1 primary key   3 , descr varchar2(30) not null
  4 );

Table created.

SQL> create sequence t1_id;

Sequence created.

SQL> create or replace trigger t1_before_insert_row   2 before insert on t1
  3 for each row
  4 begin
  5 select t1_id.nextval into :new.id from dual;   6 end;
  7 /

Trigger created.

SQL> insert into t1(descr) values ('Sample');

1 row created.

SQL> select * from t1;

        ID DESCR

---------- ------------------------------
         1 Sample

it is also possible to reference the sequence in the values clause of the INSERT (typically coded into an application, not the user's responsibility):

SQL> insert into t1(id,descr) values (t1_id.nextval,'Second');

1 row created.

SQL> select * from t1;

        ID DESCR

---------- ------------------------------
         1 Sample
         3 Second

Note that in this example, the trigger supplied the value when one was not supplied in the INSERT, and it also supplied (overwrote) a value when one was supplied by the INSERT.

++ mcs Received on Thu Mar 23 2006 - 11:43:16 CST