how I can allow duplicate values in sql?

user_12973334 0
Viewed 1836

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

sql-insert primary-key database-design oracle sql
5 Answers

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.

user_90876780

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

user_106767160

Primary Keys, must be unique, your classroom is set as primary key, so you are not allowed to add it twice.

set another variable (like an increasing id) as primary key, than you are able to add the room more often.

the question is why would you add the same information twice?

if you want to fill in the rooms into your timetable table, you don't need to add a room more than once to the GGroup

user_46276290

In the first table you declared the ClassRoom field as primary key

primary key (ClassRoom)

What that means is that the ClassRoom field is the unique identifier for the row, and that means it has to be unique. I'm not sure what your data is supposed to represent there, but if you mean that there are groups that are meeting in those class rooms, you might not want to use the class room as a unique identifier, as groups might reuse the same class room. How about creating a separate unique identifier for the table, like this (from your question looks like you are using Oracle, right?)

    CREATE TABLE GGroup(
    GGGroupId 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)
);
user_22599000

You need to add an other column primary key, like an incremented ID, and create a sequence to increment your ID. you can do it with a trigger for exemple like that:

   CREATE TABLE GGroup(
    Id NUMBER(10)    NOT NULL,
    ClassRoom varchar(7),
    GroupNum number(5),
    C_Code varchar(6),
    C_Name varchar(35), Field
    Teacher varchar(30)
);

You need to add a PRIMARY KEY constraint:

    ALTER TABLE GGroup
  ADD (
    CONSTRAINT GGroup_pk PRIMARY KEY (Id)
  );

You need also to create a SEQUENCE that will be utilized to generate the unique, auto incremented value.

    CREATE SEQUENCE GGroup_sequence;

Finaly you need a trigger which is a stored procedure that is executed when the insert occured:

    CREATE OR REPLACE TRIGGER GGroup_on_insert
  BEFORE INSERT ON GGroup
  FOR EACH ROW
BEGIN
  SELECT GGroup_sequence.nextval
  INTO :new.Id
  FROM dual;
END;
user_40468510
Related