How to write a simple query or PL/SQL code in Oracle to check if order_id exists in one table before-CodePudding

I am looking to populate a table with some data and normally I would run some insert scripts or upload via a csv file. The requirements I have is that the data can only be populated in the 2nd table as long as the order_id is within the orders table.

From what I know I probably need to write some PL/SQL code to check if the order_id exists in the orders table before running the insert scripts but not sure how to write this. I would appreciate it if somebody could get me started.

This is the create statement for the Orders Table:

CREATE TABLE ORDERS (
    ORDER_ID NUMBER NOT NULL,
    STATUS VARCHAR2(9) NOT NULL,
    ORDER_DATE DATE NOT NULL,
    PRIMARY KEY(ORDER_ID),
CONSTRAINT CHK_STATUS CHECK (STATUS = 'OPEN' OR STATUS = 'CLOSED')
);

The create statement for the 2nd table is:

CREATE TABLE ORDER2
(
    ORDER_ID NUMBER NOT NULL,
    PRODUCT_ID NUMBER NOT NULL,
    ORDER_DATE DATE NOT NULL,
    PRIMARY KEY(PRODUCT_ID)
);

Thanks.

CodePudding user response：

Well yes, you could check it manually. As HoneyBadger commented, exists is a way to do that, for example:

SQL> insert into orders (order_id, status, order_date)
  2    select 1, 'OPEN'  , trunc(sysdate - 2) from dual union all
  3    select 2, 'CLOSED', trunc(sysdate - 1) from dual;

2 rows created.

SQL> select * from orders;

  ORDER_ID STATUS ORDER_DATE
---------- ------ ----------
         1 OPEN   03.06.2022
         2 CLOSED 04.06.2022

Let's try to insert order_id = 1 into order2:

SQL> insert into order2 (product_id, order_id, order_date)
  2    select 100, 1, trunc(sysdate - 2) from dual
  3    where exists (select null
  4                  from orders
  5                  where order_id = 1);

1 row created.

It succeeded as order_id = 1 exists in orders table. What about order_id = 3 which doesn't exist there?

SQL> insert into order2 (product_id, order_id, order_date)
  2    select 300, 3, trunc(sysdate) from dual
  3    where exists (select null
  4                  from orders
  5                  where order_id = 3);

0 rows created.

SQL>

Right, nothing was inserted.

But, why wouldn't you let the database do it for you? Create a foreign key constraint which won't let any rows to be inserted into the order2 table unless that order_id exists in the orders table:

SQL> create table orders (
  2      order_id   number      constraint pk_ord primary key,
  3      status     varchar2(6) constraint chk_ord_stat check (status in ('OPEN', 'CLOSED'))
  4                             not null,
  5      order_date date        not null
  6  );

Table created.

SQL> create table order2 (
  2      product_id number      constraint pk_ord2 primary key,
  3      order_id   number      constraint fk_ord2_ord references orders (order_id)
  4                             not null,
  5      order_date date        not null
  6  );

Table created.

SQL>

Testing:

SQL> insert into order2 (product_id, order_id, order_date)
  2    values (300, 3, trunc(sysdate));
insert into order2 (product_id, order_id, order_date)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_ORD2_ORD) violated - parent key not
found


SQL>

See? Oracle won't let you do that, you don't have to check anything.

On the other hand, why two tables? Most columns are common (I presume they also share common data), so perhaps you could just add product_id into orders (I don't know whether order_id and product_id make the primary key, though):

SQL> create table orders (
  2      order_id   number,
  3      product_id number,
  4      status     varchar2(6) constraint chk_ord_stat check (status in ('OPEN', 'CLOSED'))
  5                             not null,
  6      order_date date        not null,
  7      --
  8                             constraint pk_ord primary key (order_id, product_id)
  9  );

Table created.

SQL>