0

I have a table called " Product " it has: ID_P, Name, expiration date Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, product_location_in_the_stock, ID_P (foreign key), and expiration date I want to bring the expiration date from the table product

I am new to SQL oracle so please be patient with me I am using: SQL ORACLE

UPDATE: and is it right if I do this

    CREATE TABLE stock (ID, Quantity_of_the_product,product_location_in_the_stock, ID_P foreign key)
 AS SELECT expiration_date 
FROM Product 
WHERE stock.id_p = product.id_P

1 Answer 1

0

First question - why do you want to duplicate the expiration date for any one Product across any number of Stock records? This is denormalisation and you should only do it if you really, really mean to. (If a Product's expiration date can change over time, you might need to model this differently).
Normally, you would only store the ID of the Product record and go and get the expiration date whenever you needed it. YMMV.

Assuming you do want to, something like this should do the trick:

insert into Stock 
( Quantity_of_the_product -- I'm assiming ID is an auto-incremented value 
, product_location_in_the_stock 
, ID_P 
, expiration_date
)
select 
  ? -- Stock Quantity being inserted 
, ? -- Stock Location being inserted
, p.ID_P 
, p.expiration_date 
from Product p 
where p.ID_P = ? -- product ID being inserted 
;

Edits from comments:

Products: ID_P, Name, expiration date
Stock: ID, quantity, location, ID_P (foreign key), and expiration date

Read up on Data Normalisation.
Essentially, you want a Single Source of Truth for any data item. If each product has one and only one expiration date, then that value belongs in the Product table. If you need to access that value when looking at Stock items, then you use the product foreign key in the Stock table go and look up that value:

create table products 
( ID_P ... 
, Name ... 
, expiration_date ... 
, primary key ( ID_P ) 
) 
create table stock
( ID ...
, quantity ...
, location ...
, ID_P ... 
, primary key ( ID ) 
, foreign key ID_P references products ( ID_P ) 
); 

select 
  s.ID
, s.quantity
, s.location
, p.expiration_date 
from stock s
inner join products p on s.ID_P = p.ID_P 
; 

... is it right ... CREATE TABLE stock (ID, Quantity_of_the_product, product_location_in_the_stock, ID_P foreign key) AS SELECT expiration_date FROM Product

You cannot create a record in the the stock table using [only] a select on the products table because the data values you need to insert into the stock table (like quantity & location) do not exist in the products table. You need to insert each stock record separately, with its quantity and location. As long as you leave the expiration date in the products table (where it [probably] belongs), you don't even need the sub-select:

insert into stock 
( Quantity_of_the_product
, product_location_in_the_stock
, ID_P 
) 
values 
( :quantity 
, :location 
, :product_id 
); 

Of course, you do need to know the product.id of the relevant product!

I'm assuming here that your product.id column is an auto-incrementing numeric field. If that's the case, then the database can generate a value for this field each time you run an insert on this table. (If not, you'll have to generate and insert that unique value yourself).

2
  • would you please explain how can I do this <<Normally, you would only store the ID of the Product record and go and get the expiration date whenever you needed it>> does it mean that I won't need a column of expiration date in my stock table?
    – kylie
    Jan 6, 2023 at 14:00
  • and is it right if I do this ** CREATE TABLE stock (ID, Quantity_of_the_product, product_location_in_the_stock, ID_P foreign key) AS SELECT expiration_date FROM Product WHERE stock.id_p = product.id_p**
    – kylie
    Jan 6, 2023 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.