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

Boyer, Madison 1 Reputation point
2021-03-14T15:41:34.15+00:00

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');

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. fzb 106 Reputation points
    2021-03-14T16:39:42.967+00:00

    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/

    1 person found this answer helpful.
    0 comments No comments

  2. Guoxiong 8,126 Reputation points
    2021-03-15T15:03:57.787+00:00

    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) 
    );
    
    0 comments No comments

  3. Stefan Hoffmann 621 Reputation points
    2021-03-15T15:33:14.563+00:00

    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.

    0 comments No comments

  4. qqslot qqalfa 1 Reputation point
    2022-03-24T22:46:30.23+00:00

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