CODEWITHSUNDEEP
sqloracle-database
Bigpat
Bigpat

Reputation: 1

Oracle database query issue - issues inserting data

I'm currently working on a database practice exercise but I'm having issue entering the data as I get foreign key errors.

Here are my success listed

CREATE TABLE EMPLOYEE 
(
    FNAME VARCHAR2(15) NOT NULL,
    MINIT CHAR,LNAME VARCHAR2(15) NOT NULL,
    SSN CHAR(9) NOT NULL,
    BDATE DATE,
    ADDRESS VARCHAR2(30),
    SEX CHAR(1),
    SALARY NUMBER(10,2),
    SUPER_SSN CHAR(9),
    DNO NUMBER NOT NULL,

    CONSTRAINT EMPPK PRIMARY KEY (SSN) DISABLE,
    CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPER_SSN) REFERENCES EMPLOYEE(SSN) DISABLE
);

Success the table was created

CREATE TABLE DEPARTMENT 
(
     DNAME VARCHAR2(15) NOT NULL,
     DNUMBER NUMBER  NOT NULL,
     MGR_SSN CHAR(9) NOT NULL,
     MGR_START_DATE DATE,
     CONSTRAINT DEPTPK PRIMARY KEY (DNUMBER) DISABLE,
     CONSTRAINT DEPTMGRFK FOREIGN KEY (MGR_SSN) REFERENCES EMPLOYEE(SSN) DISABLE
);

Success the table was created

ALTER TABLE DEPARTMENT ENABLE constraint DEPTPK;

Success the table altered

alter table EMPLOYEE 
  add constraint fk_d_num 
  FOREIGN KEY (DNO) references DEPARTMENT (DNUMBER);

Success the table altered

select distinct(TABLE_NAME), constraint_name, constraint_type, status
from all_constraints 
where TABLE_NAME in('EMPLOYEE', 'DEPARTMENT');

Success displayed table with status and all

Now when it comes time for the data insert

I used the command you have in the document

insert into EMPLOYEE 
values ('Jonn', 'B', 'Smith','123456789', '09-FEB-1965', '731 Fondren, Houston, TX','M', 30000, '333445555', 5)

The error I get states

Error starting at line : 1 in command -
insert into EMPLOYEE values ('Jonn', 'B', 'Smith', '123456789', '09-FEB-1965', '731 Fondren, Houston, TX', 'M', 30000, '333445555', 5)
Error report
SQL Error: ORA-02291: integrity constraint (PA96.FK_D_NUM) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
Cause: A foreign key value has no matching primary key value.
Action: Delete the foreign key or add a matching primary key.

OK so It seem that I need to work with department table first. still get the same issue

I came to a conclusion that I need to references each data I insert.

Example query hint I was given was:

INSERT INTO Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', M, 40000, 888665555, 5);

I still get a fail for the department table I tried:

INSERT INTO Department (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
VALUES ('Research', 5, 333445555, To_date('1988-05-22', ‘YYYY-MM-DD’));

I still get fail. How do I solve this issue. the date format is what I need for the department table and for the employee table but I keep getting failures.

Can someone help with this please. I need to get these loaded below

INSERT INTO Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno) 
VALUES ('Jonn', 'B', 'Smith', 123456789, '1965-02-09', '731 Fondren, Houston, TX', M, 30000, 333445555, 5)

Into the Employee table

and 

INSERT INTO Department (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
VALUES ('Research', 5, 333445555, '1988-05-22')

into the Department table.

Using Oracle.

Thank you.

Upvotes: 0

Views: 191

Answers (1)

jpw
jpw

Reputation: 44911

Just like you noticed the department needs to be inserted before the employee:

INSERT INTO Department (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
VALUES ('Research', 5, 333445555, To_date('1988-05-22', 'YYYY-MM-DD'));

INSERT INTO EMPLOYEE VALUES 
('Jonn', 'B', 'Smith','123456789', '09-FEB-1965', '731 Fondren, Houston, TX','M', 30000, '333445555', 5);

The issue with the insert into department was that this part To_date('1988-05-22', ‘YYYY-MM-DD’) contains invalid characters (the quotes around the date format literal). It should be like above.

Sample SQL Fiddle

Upvotes: 1

Related Questions