assigning data in a new table to an existing foreign key in a for loop

Question

Hi there, I was wondering if there was a way to assign new data in a table to an existing foreign key. For example if i use the following loop to assign randomly generated numbers to columns in the customer table, how would I be able to link cust_id, which I have assigned as a foreign key in the Sales table, with new data created each time a new sale is made?

CUSTOMER:
DECLARE
        v_cust_id          NUMBER(4) NOT NULL := 0000;
        v_cust_name        VARCHAR2(30);
        v_cust_add         VARCHAR2(30);
        v_phone            VARCHAR2(10);
BEGIN
        FOR v IN 1 .. 2000 --Loop 2000 times to create data for the 2000 customers in the database.
        LOOP
                v_cust_id := v_cust_id + 1;
                v_cust_name := dbms_random.string('U',5);
                v_cust_add := dbms_random.string('A',15);
                v_phone := dbms_random.value(1000000,9999999);
                        INSERT INTO customer (cust_id, cust_name, cust_add, phone)
                                VALUES (v_cust_id, v_cust_name, v_cust_add, v_phone);
        END LOOP;
END;
/

SALES:
DECLARE
        v_sale_id                 NUMBER(4) NOT NULL := ;
        v_sale_price              NUMBER(8,2);
        v_sale_date               DATE;
        v_no_of_prods             NUMBER(4);
        v_prod_id                 NUMBER(4);
        v_desp_id                 NUMBER(4);
        v_cust_id                 NUMBER(4);
BEGIN
        FOR v IN 1 .. 10
        LOOP
                v_sale_id :=
                v_sale_price
                v_sale_date :=
                v_no_of_products :=
                v_prod_id :=
                v_desp_id :=
                v_cust_id :=
                        INSERT INTO sales (sale_id, sale_price, sale_date, no_of_prods, prod_id, desp_id, cust_id)
                                VALUES (v_sale_id, v_sale_price, v_sale_date, v_no_of_prods, v_prod_id, v_desp_id, v_cust_id);
        END LOOP;
END;
\

THANKS!

Answer 1

It looks like your customer id's range from 1-2000, couldn't you assign dbms_random.value(1,2000); to it for each sale and be guaranteed to find an existing row to reference? If not, can you clarify the question more?

Answer 2

You are generating test data to do some kind of performance test?

Let's first generate 2000 customers.

(untested)

insert into customer 
(cust_id, cust_name, cust_add, phone)
select 
level l, 
dbms_random.string('U',5), 
dbms_random.string('A',15), 
dbms_random.value(1000000,9999999) 
from dual
connect by level <= 2000;

Now you can genereate sales data by selecting from the customer table:

insert into sales 
(sale_id, sale_price, sale_date, no_of_prods, prod_id, desp_id, cust_id)
select sale_id_sequence.nextval , dbms_random. ...., cust_id
from customer;

insert into sales 
(sale_id, sale_price, sale_date, no_of_prods, prod_id, desp_id, cust_id)
select sale_id_sequence.nextval , dbms_random. ...., cust_id
from customer
where mod(cust_id,2) =0;

insert into sales 
(sale_id, sale_price, sale_date, no_of_prods, prod_id, desp_id, cust_id)
select sale_id_sequence.nextval , dbms_radom. ...., cust_id
from customer
where mod(cust_id,7) =0;

insert into sales 
(sale_id, sale_price, sale_date, no_of_prods, prod_id, desp_id, cust_id)
select sale_id_sequence.nextval , dbms_random. ...., cust_id
from customer
where mod(cust_id,13) =0;

commit;

I assume there is a sequence to create a sale id.

edit1 improvement:

create table customer 
( cust_id number(10)
, cust_name varchar2(50)
, cust_add  varchar2(30)
, cust_phone varchar2(10)
);

create sequence cust_id_seq;

create table sales
( sale_id number(10)
, prod_no number(10)
, cust_id number(10)
);

create sequence sale_id_seq;

begin

  insert into customer 
  select cust_id_seq.nextval 
  ,      dbms_random.string('U',5)
  ,      dbms_random.string('A',15) 
  ,      trunc(dbms_random.value(1000000,9999999))
  from   dual
  connect by level < 2000;

  for i in 1..10 loop

    insert into sales 
    select sale_id_seq.nextval
    ,      trunc(dbms_random.value(1,100))
    ,      cust_id
    from   customer;

    insert into sales 
    select sale_id_seq.nextval
    ,      trunc(dbms_random.value(1,100))
    ,      cust_id
    from   customer
    where  mod(cust_id+i,2)=0;

    insert into sales 
    select sale_id_seq.nextval
    ,      trunc(dbms_random.value(1,100))
    ,      cust_id
    from   customer
    where  mod(cust_id+i,7)=0; 

  end loop;

end;
/

commit;

select count(*) from customer;

select count(*) from sales;

Answer 3

Hi there, I was the person who posted the question above. I will be using the random generator to assign values to the sales table as well. But what I was wondering is how you would assign a cust_id to newly generated data into the sales table. Also, what does the line: where mod(cust_id,13) =0; mean? Thank you.

Answer 4

Your question has not become any clearer. It would help us to help you if you explained the scenario you are trying to implement. In real life you would not - or should not - have a SALES record without a prior existing CUSTOMER record. So, are you trying to generate test data or implementing business logic?

Answer 5

Yeah, I can see that what I'm trying to say makes no sense because it makes no sense to me as well. I need to create test data for an assignment where I have to create a database system. So far I have run my for loop to create test data for my customer table, so there is now currently existing customer records. Now what I have to do is create test data for the sales table. What I want to do is find a way of linking cust_id into the sales table. I have set cust_id as a foreign key into the sales table. But I was just thinking whether I had to create a separate procedure to do this, that may include a cursor?

Answer 6

OMG! Thank you so much. You are a life saver! Hopefully I can do the same for other foreign keys within my database. THANK YOU again. Sorry, just another thing, how would I alter that loop if the sales table included sale_prod_name varchar2(30)?