(Oracle Database 11g) unique constraint violated coming in INSERT/UPDATE

Question

Why do i still get a error even I made my value unique? What I did was first to create the table including the primary keys, and insert the values inside the tables and now I'm trying to add the foreign keys and that's where the problem starts to occur, when I try to alter the table DEPT_Table

create table DEPT_Table(
Dname varchar(15),
Dnumber varchar(6),
Mgr_num varchar(10),
EMP_START_DATE date);

and insert this

insert into DEPT_Table values ('HRAdmin','DTHRA1','MHRA111',TO_DATE('1/8/2017','MM/DD/YYYY'));

and try to connect it to my DEPT_Loc

create table Dept_Loc(
  Dnumber varchar(8),
  DLocation varchar(8));

insert into Dept_Loc values('DLHRA1','BLDG1F2');

It still gets a error, I already tried to make my values unique as possible and each of the respective Departments are named "DTHRA1" for the Dept_Tables and "DLHRA1" for the department location but it still gets the error ORA-00001: unique constraint violated

what can i do to fix this?

Ed Bangga · Accepted Answer · 2019-10-15 01:28:35Z

2

Seems there's no "DLHRA1" in your primary table. I think you are referring to "DTHRA1"

insert into Dept_Loc values('DTHRA1','BLDG1F2');

answered Oct 15, 2019 at 1:28

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

3 Comments

Borgaro Over a year ago

so I must have "DLHRA1" in my primary? but I thought when doing foreign key and referring it the value of the FK is unique? what i did before was there is no DLH and DTH, only DHRA1 for both tables

Ed Bangga Over a year ago

from what i understand from your problem is that you are trying to insert DLHRA1 in your Dept_Loc, but wont allow you because this doesn't exist in your primary table Dept_Table, your Dept_Loc->Dnumber has foreign key in your Dept_TAble->DNumber

Borgaro Over a year ago

I think this will be harder without visualization, please check this screenshot: prntscr.com/pk07lr I made you a visualization in excel to make it easier, and also for you to full view of my work in progress the ID I used in excel was before I try to alter the table so they all look the same

Collectives™ on Stack Overflow

(Oracle Database 11g) unique constraint violated coming in INSERT/UPDATE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related