Oracle SQL Errors

Question

I am very new to SQL and my class is using Oracle cloud as of now. I have created tables and I am trying to insert data into it. I keep receiving the error ORA-02291: integrity constraint violated when I try to insert data into my semester table. Any help would be extremely appreciated. Thank you!

CREATE TABLE SCHOOL (
  SchoolID              char(10)        NOT NULL,
  SchoolName            char(50)        NOT NULL,
  SchoolAddress         char(50)        NOT NULL,
  SchoolCity            char(50)        NOT NULL,
  SchoolState           char(2)         NOT NULL,
  SchoolZIP             char(30)        NOT NULL,
  SchoolPhone           char(25)        NULL,
  SchoolWebsite         char(50)        NOT NULL,
    CONSTRAINT SCHOOL_PK PRIMARY KEY(SchoolID)
);

CREATE TABLE SEMESTER(
SemesterName            char(20)        NOT NULL,
Year                char(4)         NOT NULL,
SchoolID            char(50)        NOT NULL,
    CONSTRAINT SEMESTER_PK PRIMARY KEY(SemesterName),
        CONSTRAINT SEM_SCH_FK FOREIGN KEY(SchoolID)
            REFERENCES SCHOOL(SchoolID)
);

/****************************** School ******************************************/
INSERT INTO SCHOOL(SchoolID, SchoolName, SchoolAddress, SchoolCity, SchoolState, SchoolZip, SchoolPhone, SchoolWebsite)
    VALUES(
        'NIU', 'Northern Illinois University', '1425 Lincoln Hwy', 'Dekalb', 'IL', '60115', '815-753-0446', 'https://myniu.niu.edu/');
SELECT * FROM SCHOOL;

/****************************** Semester ****************************************/

INSERT INTO SEMESTER(SemesterName, Year, SchoolID)
    VALUES(
        'Spring', '2020', 'NIU');
INSERT INTO SEMESTER VALUES(
        'Fall', '2020', 'NIU');
SELECT * FROM SEMESTER;

What is the actual error message? It should be something like "integrity constraint <constraint name> violated – parent key not found" — Phil
– Phil, Commented Apr 2, 2020 at 23:56
Also, your foreign key data-types should match. At the moment, SCHOOL.SchoolID is char(10) and SEMESTER.SchoolID is char(50) — Phil
– Phil, Commented Apr 2, 2020 at 23:58
Hi, the error I am receiving is ORA-02291: integrity constraint (ADMIN.SEM_SCH_FK) violated - parent key not found. — Vishwas Rao
– Vishwas Rao, Commented Apr 3, 2020 at 0:01

vishnudattan · Accepted Answer · 2020-04-03 01:10:22Z

Check if there are trailing spaces in the SCHOOLID column

select schoolid, length(schoolid) from school;

You would most likely see length as 10 instead of 3 :

|SCHOOLID   |LENGTH(SCHOOLID) |
|NIU        |10               |

A good explanation is below ( quoting Tom Kyte here )

A varchar2 datatype, when stored in a database table, uses only the space allocated to it. If you have a varchar2(1999) and put 50 bytes in the table, we will use 52 bytes (leading length byte).

A char datatype, when stored in a database table, always uses the maximum length and is blank padded. If you have char(1999) and put 50 bytes into it, it will consume 2001 bytes (leading length field is present on char's as well).

In the database -- a CHAR is a VARCHAR that is blank padded to its maximum length.

There are great differences between a char(1999) and varchar2(1999) as the CHAR is always 1999 characters long physically AND logically. the varchar is physically 1999 bytes but logically can be any size in between 0 and 1999. The char is always blank padded, not so for the varchar.

See if the error persists when you define columns as varchar2, as follows ..


CREATE TABLE SCHOOL (
  SchoolID             varchar2(10)        NOT NULL,
  SchoolName           varchar2(50)        NOT NULL,
  SchoolAddress        varchar2(50)        NOT NULL,
  SchoolCity           varchar2(50)        NOT NULL,
  SchoolState          varchar2(2)         NOT NULL,
  SchoolZIP            varchar2(30)        NOT NULL,
  SchoolPhone          varchar2(25)        NULL,
  SchoolWebsite        varchar2(50)        NOT NULL,
    CONSTRAINT SCHOOL_PK PRIMARY KEY(SchoolID)
);

CREATE TABLE SEMESTER(
SemesterName            varchar(20)        NOT NULL,
Year                varchar(4)         NOT NULL,
SchoolID            varchar(50)        NOT NULL,
    CONSTRAINT SEMESTER_PK PRIMARY KEY(SemesterName),
        CONSTRAINT SEM_SCH_FK FOREIGN KEY(SchoolID)
            REFERENCES SCHOOL(SchoolID)
);

Or replace the data type varchar2(50) with constraint see_sch_fk references school(schoolid) and let it inherit the type :) But I agree, the problem is char.

Collectives™ on Stack Overflow

Oracle SQL Errors

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related