Re: Referencing an Oracle Sequence

A few notes:

1. don't use VARCHAR2 for numeric ID columns. Use NUMBER instead. Will save you a lot of trouble with implicit datatype conversions and will be stored and indexed a bit more optimally.
2. Answer to your question: If you are inserting an order, you should already know the customer_id of the customer who placed it. You don't need to use sequence for this. Sequence is only needed for initial (guaranteed to be unique) customer_id generation (something like MSSQL's AutoIncrement type, but a bit more flexible). All detail tables should use value stored in master table for references. You could do this in SQL*Plus like this:

SQL> var custid number;
SQL> insert into customers values( custid_seq.nextval ) 2> returning customer_id into :custid
3> /

Now you have custid host variable holding the newly created customer's ID. I hope you will figure out the rest by yourself. :)

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"casper44" <dkniveton71_at_msn.com> wrote in message
news:be3be537.0204131325.111db2c5_at_posting.google.com...


> If I have an Oracle sequence identified as a primary key in one table

> how do I reference that sequence in another table.  For example I have

> customer_id as a sequence and primary key in the customer table.  Then

> I want to reference customer_id in the orders table by making

> customer_id a column in the orders table and a FK reference the PK the

> in customer table.  What I really want to know is when I use insert

> statements to enter the information for the orders table what do I put

> in for customer_id?

>

> create table CUSTOMER (

> customer_id VARCHASR(10),

> constraint CUSTOMER_ID_PK PRIMARY KEY (customer_id))

>

> CREATE sequence customer_id

> increment by 1

> start with 10000

> NOCACHE

> ORDER;

>

> insert into orders values (customer_id.NextVal);

>

> Thanks.

>

> create table ORDERS (

> orders_id VARCHAR2(10),

> customer_id VARCHASR(10),

> constraint ORDERS_ID_PK PRIMARY KEY (orders_id),

> constraint ORDERS_FK

>   FOREIGN KEY(customer_id) REFERENCES customer (customer_id));

>

> insert into orders values ('0000000001','<??WHAT GOES HERE??>');