how I can allow duplicate values in sql?

Question

so i have this code with simple two tables 1-GGroup 2-TimeTable

and this is the code for them :

CREATE TABLE GGroup(
    ClassRoom varchar(7),
    GroupNum number(5),
    C_Code varchar(6),
    C_Name varchar(35), Field
    Teacher varchar(30),
    primary key (ClassRoom)
);

CREATE TABLE TimeTable(
    ClassRoom varchar(7),
    StudentID number(9),
    FirstName varchar(30),
    LastName varchar(30),
    primary key(ClassRoom, StudentID),
    foreign key(ClassRoom) references GGroup(ClassRoom)
);

And i already inserted rows in table GGroup with np!

But now im trying to insert this row

insert into GGroup values (
    'A/3/54', 
    1608, 
    'ISM223', 
    'Data Warehouse & Data Mining', 
    'Dr. Yasser Al-Mshhor'
);

And i got this error:

ORA-00001: unique constraint (SQL_XAKKMDKZQBPBDDQFTDEXENGDH.SYS_C0025290829) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

I think its because this row I inserted before:

insert into GGroup values (
    'A/3/54', 
    1608, 
    'ISM223', 
    'Data Warehouse & Data Mining', 
    'Dr. Yasser Al-Mshhor'
);

How i can fix this ? I dont know alot about sql

Answer 1

As others have already pointed out in their answers above - you are having this issue because you're using ClassRoom column as the Primary Key in your GGroup table script i.e. primary key (ClassRoom).

What is PRIMARY KEY?

• A table can have only ONE primary key.
• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.

This means that you cannot insert a duplicate value i.e. A/3/54 in ClassRoom column. One of the easiest ways to resolve this error will be to add another column e.g. GroupId by altering or dropping the table.

Note: If you have many rows in GGroup table then use ALTER TABLE (already shown in previous answers) statement than using DROP TABLE statement.

Step 1:

DROP TABLE GGroup;

Step 2:

CREATE TABLE GGroup(
    GroupId NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    ClassRoom varchar(7),
    GroupNum number(5),
    C_Code varchar(6),
    C_Name varchar(35), Field
    Teacher varchar(30)
);

Once both the steps are followed, you can then insert duplicate values without getting any ORA-00001: unique constraint error.

Answer 2

Table GGroup defines column ClassRoom as the primary key. This explicitly disallows duplicates in this column. Your code fails because you are trying to insert a record with ClassRoom 'A/3/54', while another record already exists in the tabe with the same value in that column.

Possible options:

• change the primary key of the column to something else, that better represents your use case

• change the value of ClassRoom in the newly inserted record to a value that does not yet exist in the table

• ignore this error: since both records seem to be complete duplicates, that might be your best pick here