SQL- Can someone tell me how to fix this error?

Question

Error Code: Error starting at line : 345 in command - INSERT INTO ONLINE_REVIEWS VALUES ('5', 'Chau', 'Clement', '21', 'Yelp') Error report - ORA-00001: unique constraint (ORADB2.STAR_RATING_PK) violated

**It adds the first 4 then I get this for the rest of the lines

CREATE TABLE ONLINE_REVIEWS
(STAR_RATING VARCHAR2 (30),
LAST_NAME VARCHAR2 (30),
FIRST_NAME VARCHAR2 (30),
TRIP_ID VARCHAR2 (40),
WEBSITE VARCHAR2 (30),
CONSTRAINT STAR_RATING_PK PRIMARY KEY (STAR_RATING) );

INSERT INTO ONLINE_REVIEWS VALUES ('4', 'Northfold', 'Liam', '40', 'TripAdvisor');
INSERT INTO ONLINE_REVIEWS VALUES ('5', 'Northfold', 'Liam', '21', 'TripAdvisor');
INSERT INTO ONLINE_REVIEWS VALUES ('3', 'Caron', 'Jean Luc', '38', 'TripAdvisor');
INSERT INTO ONLINE_REVIEWS VALUES ('2', 'Chau', 'Clement', '12', 'Yelp');
INSERT INTO ONLINE_REVIEWS VALUES ('5', 'Chau', 'Clement', '21', 'Yelp');
INSERT INTO ONLINE_REVIEWS VALUES ('2', 'Brown', 'Brianne', '12', 'Yelp');
INSERT INTO ONLINE_REVIEWS VALUES ('3', 'Jones', 'Laura', '32', 'TripAdivisor');
INSERT INTO ONLINE_REVIEWS VALUES ('2', 'Gernowski', 'Sadie', '4', 'Yelp');
INSERT INTO ONLINE_REVIEWS VALUES ('4', 'Bretton-Borak', 'Siam', '11', 'Yelp');
INSERT INTO ONLINE_REVIEWS VALUES ('3', 'Bretton-Borak', 'Siam', '38', 'Yelp');
INSERT INTO ONLINE_REVIEWS VALUES ('4', 'Northfold', 'Liam', '40', 'TripAdvisor');

Answer 1

you use the STAR_RATING column as primary key, primary keys have to be unique. so in the 5th line you try to add a "5" the second time, which fails. you might want to use an identity column as primary key https://www.oracletutorial.com/oracle-basics/oracle-identity-column/

Answer 2

I facing the same issue, somehow trying the things u guys suggested wont work ?

Answer 3

At the first glance: Normalize your data model...

The primary key normally ensures that only one review can be given by one reviewer per topic in such a scenario. Neither the reviewer nor the topic are clearly identifiable in the current schema.
The person behind LAST_NAME and FIRST_NAME should be normalized (2NF),
The website be normalized (2NF or DKNF),
Is there a functional dependency between person, trip and website? I would guess yes. Then you need further normalization.

I would expect PERSON_ID, TRIP_ID to be the primary key in a simplified model.

Answer 4

You can not use the STAR_RATING column as the primary since the data in this column is not unique. You can add an ID column with IDENTITY and set it to the primary key. And also it is not a good practice to name the primary key with the column name appended with _PK in case if you want to set the column with the same name in the another table to the primary key.

CREATE TABLE ONLINE_REVIEWS
(
    ID NUMBER GENERATED ALWAYS AS IDENTITY,
    STAR_RATING VARCHAR2 (30),
    LAST_NAME VARCHAR2 (30),
    FIRST_NAME VARCHAR2 (30),
    TRIP_ID VARCHAR2 (40),
    WEBSITE VARCHAR2 (30),
    CONSTRAINT PK_ONLINE_REVIEWS PRIMARY KEY (ID) 
);