Oracle PL/SQL Multiple insert without duplicate value

Question

I have table call reviewbook table. Users need to assign 3 different review members to a bookid. They can be any reviewmemberid as long as they are 3 different ones to one bookid. Below is the result that I want. Right now, what i can think of is doing a multiple insert in a query however, the reviewmemberid can be duplicated, which is not what I want. I heard that trigger can be a way of doing this but I have no idea how to apply that. Can someone be kind enough to guide me or maybe if there is a better way of doing this.

   Reviewbook
   -reviewid
   -reviewmemberid
   -bookid

  reviewid  reviewmemberid  bookid
      1           2           1     
      2           3           1
      3           5           1
      4           1           2 
      5           2           2
      6           5           2
      7           1           3
      8           2           3
      9           4           3    



//My current insert code, but this insert code can insert duplicate reviewmemberid.
INSERT ALL INTO REVIEW (REVIEWID,REVIEWMEMBERID,BOOKID)VALUES
(?.?,?) INTO REVIEW
(REVIEWID,REVIEWMEMBERID,BOOKID)VALUES 
(?.?,?) INTO REVIEW
(REVIEWID,REVIEWMEMBERID,BOOKID)VALUES 
(?.?,?) SELECT * FROM DUAL;

Do you simply want to make sure that each combination of (REVIEWMEMBERID,BOOKID) appears only once, or is it more complicated than that? — Darwin von Corax
– Darwin von Corax, Commented Apr 4, 2016 at 3:59
Yes so 3 different reviewmemberids to one book id and that bookid is done. Users will proceed on assigning for the next bookid. — Jason Ching Yuk
– Jason Ching Yuk, Commented Apr 4, 2016 at 4:04
Would it be enough to put a unique index on (REVIEWMEMBERID,BOOKID)? — Darwin von Corax
– Darwin von Corax, Commented Apr 4, 2016 at 4:05
There is no PL/SQL in your question. Why do you think you need a stored procedure? — user330315
– user330315, Commented Apr 4, 2016 at 6:03

Darwin von Corax · Accepted Answer · 2016-04-04 04:18:54Z

0

Another option would be to simply declare a unique index on the columns (REVIEWMEMBERID,BOOKID). The syntax would be

CREATE UNIQUE INDEX book_reviewer_idx
  ON review (reviewmemberid, bookid);

This will ensure that a particular pairing of (reviewmemberid, bookid) appears only once in the table; an attempt to insert a duplicate will raise an error.

answered Apr 4, 2016 at 4:18

Darwin von Corax

5,2643 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Dimitriadis Over a year ago

Adding the constraint doesn't answer how to add without duplicates on the example in question.

Darwin von Corax Over a year ago

Sure it does. The constraint prevents any combination of reviewmemberid, bookid will appear only once, which is what the OP wanted.

Collectives™ on Stack Overflow

Oracle PL/SQL Multiple insert without duplicate value

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related