Member Avatar for tony75

How can I insert two row to supplier table?

CREATE TABLE supplier (
    supplier_nr NUMBER(10),

    supplier_id VARCHAR2(20) NOT NULL,

    price   NUMBER(10) NOT NULL,

    CONSTRAINT supplier_supplier_nr_pk PRIMARY KEY(supplier_nr),
    CONSTRAINT supplier_supplier_id_fk FOREIGEN KEY(supplier_id)
        REFERENCES varugrupp(supplier_id)
);
  • 2 Contributors
  • 4 Replies
  • 312 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by tony75

Dani AI

Generated

A short clarification and a couple of safe ways to insert two rows.

: was correct in principle — each new row must have a distinct primary key and any foreign-key value must already exist in the referenced parent table (otherwise Oracle raises a "parent key not found" error); likewise inserting a duplicate primary-key value raises a unique-constraint error. (docs.oracle.com)

To insert two rows in one statement in Oracle use a multitable/multirow insert (INSERT ALL). Example:

INSERT ALL
  INTO supplier (supplier_nr, supplier_id, price) VALUES (101, 'GRP_A', 10)
  INTO supplier (supplier_nr, supplier_id, price) VALUES (102, 'GRP_B', 12)
SELECT * FROM dual;

This executes as a single statement and is the standard Oracle way to add multiple literal rows at once. (docs.oracle.com)

An alternate pattern uses a subquery with UNION ALL (useful if you prefer a single INSERT ... SELECT):

WITH vals AS (
  SELECT 101 supplier_nr, 'GRP_A' supplier_id, 10 price FROM dual UNION ALL
  SELECT 102, 'GRP_B', 12 FROM dual
)
INSERT INTO supplier (supplier_nr, supplier_id, price)
SELECT supplier_nr, supplier_id, price FROM vals;

Always list the columns in the INSERT (don’t rely on column order) and COMMIT when appropriate. (docs.oracle.com)

Notes and troubleshooting: the CREATE TABLE posted had a spelling mistake in the FK clause (FOREIGEN); use FOREIGN KEY. If you ever need to insert mutually-referencing rows (self-referential FK) consider declaring the FK DEFERRABLE/INITIALLY DEFERRED or insert the parent row first — Oracle documents how deferrable constraints work. (docs.oracle.com)

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

Insert the rows with a unique supplier_nr for each row and an existing supplier_id.

Member Avatar for tony75

Can you give me one example please!

Member Avatar for pritaeas 
INSERT INTO supplier VALUES (1, 'ID', 10)

The values shown above depend on what is already in your tables, so you may need to change those. For example 'ID' needs to be in the varugrupp table.

Edited by pritaeas
Member Avatar for tony75

THANKS SIR

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.